Print Mail Address from Excel

[JimmyC]

Using Win xp sp 3; Excel xp;
I usually convert my Excel data into Access and print mailing labels from Access. But I don't need mailing labels this time...I need to print the address on paper directly using Excel only. In Sheet1, I have 1042 rows of data in Columns a through e. Column a has the name; column b is address line # 1; column c is address line # 2; column d is city, state, and zip code and column e has to the "attention of XXXX" and is more often than not blank.

I need to take the columns in sheet 1, row 1, and copy the data in each column to sheet 2 as follows:
Sheet1 a1 to Sheet2 e15
Sheet1 b1 to Sheet2 f15
Sheet1 c1 to Sheet2 g15
Sheet1 d1 to Sheet2 h15
Sheet1 e1 to Sheet2 i15

I need to print sheet 2 and then clear the values in cells e15 through i 15. Next I need to move to row 2, column a on sheet1 and do everything over again. Do I need to create a range on sheet1 to make a macro work?

I can't out how to make the cell pointer move to the right, then copy that value to sheet2 in the correct cell location and then move the celll pointer on sheet1 to the right again and again, until I reach column e. I also need to try to make the macro so that it can be applied to different sheets as I have 21 other workbooks with sheets that have between 1042 rows and 3,037rows of data in columns a through e.

I guess I have taken for granted how easy it is to print mailing labels through access from data the originated in an Excel workbook. I am stuck in trying to complete the above process through Excel. My macro skills struggle {stink} when I can't use the macro recorder to help me get started with code. I can't figure how to move the cell pointer in sheet1 one column to the right, then stop when it reaches column i, print sheet2, clear the values in sheet2 and then move back to sheet1 and move the cell pointer down a row and back to column a to start the process all over again. Finally, how does a macro "know" when it reaches a blank row {i.e. the cell in column A is blank} so that it quits execution? THANKS.
JimC

[HansV]

Try this:

Code: Select all

Sub PrintLabels()
  Dim wsh1 As Worksheet
  Dim wsh2 As Worksheet
  Dim r As Long
  Dim m As Long
  ' Modify sheet names as needed
  Set wsh1 = ActiveWorkbook.Worksheets("Sheet1")
  Set wsh2 = ActiveWorkbook.Worksheets("Sheet2")
  ' Determine last used row
  m = wsh1.Range("A" & wsh1.Rows.Count).End(xlUp).Row
  ' Loop through the rows
  For r = 1 To m
    ' Copy cells in columns A:E to target sheet E15:I15
    wsh1.Range("A" & r & ":E" & r).Copy _
      Destination:=wsh2.Range("E15")
    ' Print target sheet
    wsh2.PrintOut
  Next r
End Sub

[JimmyC]

Hans,
You are a life saver...I have learned lots of things from your code...It will be saved in a workbook for future use and reference......But I am most intrigued as to how you calculated "m"....going to the bottom of the worksheet and then moving the cell pointer up until it hits a non-empty cell...Golly, in a million years I wouldn't have gotten that one. Thank you so much.
JimC

[HansV]

... how you calculated "m"....going to the bottom of the worksheet and then moving the cell pointer up until it hits a non-empty cell.

That is a very useful technique. It will work in all versions of Excel, since it uses wsh1.Rows.Count instead of a literal number (65,536 for Excel 97-2003, or 1,048576 for Excel 2007-2010).

Also note that it wasn't necessary to select any cell in the code - in many situations, one can refer to cells without actually selecting them.

[JimmyC]

Hans,
Thanks so much for the code and your explanation. I have learned so much from you.. Again, thank you.
JimC