VBA - Copy cell from Sheet 2, paste into Sheet 1, print and return to next value in Sheet 2

[harlequeens]

Hi

I am sure for anyone with a modicum of VBA skills, this will be a sinch, but for someone like me who last tried macros using Lotus 1-2-3, I'm struggling.

I would like to copy the values contained in 3 particular cells, A9, B9 and P9 from Sheet 2 of a spreadsheet to cells B2, B3 and D2 respectfully on Sheet 1 of the same spreadsheet, (which is formatted to a particular shape and size of label, with multiple labels on the same sheet, when it prints), where column A is a first name, column B is a last name, and column P is a location.

Then I want to copy the next person's values contained in the next row of sheet 2, i.e. cells A10, B10 and P10 from Sheet 2 of the spreadsheet to cells B2, B3 and D2 respectfully on Sheet 1 again thereby overwriting the data that has already been printed. I then want to print the sheet again with the new values. This process would be repeated until the first blank cell in column A of sheet 2 is reached.

Any help would be gratefully appreciated.

Thanks

Mike

 

[JLGWhiz]

See if you can use this.

Sub labels()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 9 To lr
        sh1.Range("B2") = sh2.Cells(i, 1)
        sh1.Range("B3") = sh2.Cells(i, 2)
        sh1.Range("D2") = sh2.Cells(i, 16)
        sh1.PrintOut
        s = Timer + 2
            Do While Timer < s
                DoEvents
            Loop
    Next
End Sub