How to Create Macro to Increase Cell Value to one and Print Worksheet

[mdms515]

We are trying to automate the printing of a worksheet. In sheet
"Jobs" of the workbook it has consecutive numbers in column A (1,
2,3 ...) and is variable. We would like to automate the insertion of
each consecutive number from this column A into cell C6 of the Summary
sheet, and print out the Summary sheet, until the last number is
reached in column A.

Example: Column A has numbers 1-32. The macro will copy 32 times
(numbers 1 through 32) into cell C6 and each time print the Summary
sheet out. (by copying the number into C6 it will activate other
formulas...)

Thanks so much for anyone who can assist!

 

[Gary''s Student]

Sub printum()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Jobs")
Set s2 = Sheets("Summary")
n = s1.Cells(Rows.Count, "A").End(xlUp).Row
s2.Activate
For i = 1 To n
s2.Range("C6").Value = s1.Cells(i, "A").Value
Application.Calculate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next
End Sub

 

[mdms515]

Sub printum()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Jobs")
Set s2 = Sheets("Summary")
n = s1.Cells(Rows.Count, "A").End(xlUp).Row
s2.Activate
For i = 1 To n
s2.Range("C6").Value = s1.Cells(i, "A").Value
Application.Calculate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next
End Sub

Gary, thank you so much!! What if we what to modify the formula to
account for a slightly different name than "Jobs" for the Jobs sheet;
sometimes it contains an additional code i.e. "Jobs 34-08".

 

[Gary''s Student]

just change the line:

Set s1 = Sheets("Jobs")

into:

Set s1 = Sheets("Jobs 34-08")

or whatever the tab name happens to be.

 

[mdms515]

just change the line:

Set s1 = Sheets("Jobs")

into:

Set s1 = Sheets("Jobs 34-08")

or whatever the tab name happens to be.

Gary,
Thanks for your quick response. I am sorry I may have been not
totally clear. The tab name may change from day to day - so we are
looking for a formula to account for that. The word "jobs" will
always be part of the name though. Is it possible to include this as
a string? Or: there are only two tabs, the first one containing the
"Jobs" name, and the second one is always the same.