Excel 2002 printing entries on separate pages

Question

I have a worksheet that contains records for over 1000 staff. Within the workbook is another sheet that after entering a unique id will populate with the data from the first sheet in an easier to read format and that can be printed. Occasionally I need to print a copy of the data for all staff to verify.

I could create the following macro for each row but this would mean data for empty rows being printed.

Sub PrintPHandSRecord() ' ' PrintPHandSRecord Macro ' Macro recorded 10/05/2010 by Paul W Morley '

'

   Sheets("Command_H&S_Records").Select
   Range("C2").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Personal_H&S_Record").Select
   Range("C4").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Application.CutCopyMode = False
   ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
   Sheets("Command_H&S_Records").Select
   Range("C3").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Personal_H&S_Record").Select
   Range("C4").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Application.CutCopyMode = False
   ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

How can I automate the copy next cell part of the macro so that the print would look at C2, C3, C4 etc in turn printing out for each record?

Answer

I¡¯d probably do this via mail merge using Word, but that said, this macro should do what you want ¨C I've done away with copy and paste (I'm never keen on that in a macro) and simply got it to loop through the records

Sub PrintPHandSRecord() '

' Dim Looper as long

  Sheets("Personal_H&S_Record").Select

for looper = 2 to Sheets("Command_H&S_Records").cells.SpecialCells(xlCellTypeLastCell).row Range("C4").value= Sheets("Command_H&S_Records").cells(looper,3).value If len(trim(range("C4").value))>0 then ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True next End Sub

(I assumed your "empty rows" related to data on column C of the main sheet