Loop Macro

[KalliKay]

I have a template that I want to fill in a cell from another worksheet
(project #) and then print the template which fills in other pertenent
information. The project # range is variable. How can I create a loop to
select the first cell, copy and paste to the template, print the template and
then repeat the process until all project #s have been selected? Thanks so
much.

KK

 

[Per Jessen]

Hi KK

Wih this macro I assume that Project# workbook is open, and project# is in
column A starting in row 2. TargetCell is the project# cell in the template.

Sub test()
Dim ProjectWB As Workbook
Set ProjectWB = Workbooks("Project#.xls")
TargetCell = "A1" ' change to suit
ProjectNumCol = "A"
StartRow = 2 ' Assuming headings in row 1
LastRow = ProjectWB.Worksheets("Sheet1"). _
Cells(StartRow, ProjectNumCol).End(xlDown).Row
For r = 1 To LastRow
Range(TargetCell) = ProjectWB.Worksheets _
("Sheet1").Cells(r, ProjectNumCol)
ThisWorkbook.PrintOut
Next
End Sub

Regards,
Per

 

[KalliKay]

Thanks so much. I used some of your code in combination with some other and
it worked great.

KK