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!

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''s Student - gsnu2007j


" wrote:

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!

On Jul 23, 11:50 am, Gary''s Student
wrote:
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''s Student - gsnu2007j

" wrote:
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, 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".

just change the line:

Set s1 = Sheets("Jobs")

into:

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

or whatever the tab name happens to be.
--
Gary''s Student - gsnu2007j


" wrote:

On Jul 23, 11:50 am, Gary''s Student
wrote:
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''s Student - gsnu2007j

" wrote:
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, 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".

On Jul 24, 1:18 pm, Gary''s Student
wrote:
just change the line:

Set s1 = Sheets("Jobs")

into:

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

or whatever the tab name happens to be.
--
Gary''s Student - gsnu2007j

" wrote:
On Jul 23, 11:50 am, Gary''s Student
wrote:
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''s Student - gsnu2007j

" wrote:
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, 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,
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.