ExcelBanter - Printing Multiple Sheets

The following example should give you a lead as to how to attack the problem.
You need to save each range to be printed in cells somewhere out of the way
in a worksheet. When you want to print them, retrieve the ranges and set the
print area with the multiple retrieved ranges.

The first procedure below saves the ranges and then the second one retrieves
them and sets the print area.

If you can set all the individual ranges for printing without exiting the
procedure to perform manual work then you could run the whole lot as one
macro without saving the individual ranges to a worksheet. Simply save them
to the variables and you then would not have to retrieve them. The way I have
done it allows you to run whatever procedure you want for each individual
section of the print and save its range.

Do the print setup as per the comments at the bottom of the second procedure.

The ranges I have used in the example would all fit on one sheet if selected
as one single print range but as multiple ranges in the print area , they
print on separate sheets. I don't think that you can alter that. However,
they do number consecutively.

Dim PrtRnge1 As Range
Dim PrtRnge2 As Range
Dim PrtRnge3 As Range
Dim PrtRnge4 As Range
Dim FullPrtRnge As Range

Sub Save_Multi_Print_Ranges()
'Save each individual range to cells on a worksheet

Sheets("Sheet1").Select
Set PrtRnge1 = Range("A1:D7")
Sheets("Sheet1").Range("AA1") = PrtRnge1.Address
Set PrtRnge2 = Range("C18:G25")
Sheets("Sheet1").Range("AA2") = PrtRnge2.Address
Set PrtRnge3 = Range("B30:H37")
Sheets("Sheet1").Range("AA3") = PrtRnge3.Address
Set PrtRnge4 = Range("C44:E51")
Sheets("Sheet1").Range("AA4") = PrtRnge4.Address
End Sub

Sub Retrieve_Multi_Print_Ranges()
'Retrieve the individual print ranges
'and the set print area
Sheets("Sheet1").Select
Set PrtRnge1 = Sheets("Sheet1").Range(Range("AA1"))
Set PrtRnge2 = Sheets("Sheet1").Range(Range("AA2"))
Set PrtRnge3 = Sheets("Sheet1").Range(Range("AA3"))
Set PrtRnge4 = Sheets("Sheet1").Range(Range("AA4"))
Set FullPrtRnge = Application.Union _
(PrtRnge1, PrtRnge2, PrtRnge3, PrtRnge4)
ActiveSheet.PageSetup.PrintArea = FullPrtRnge.Address

'After running this macro to this point, stop it
'here and then simply record the page set up for
'your print headers and page numbering etc and
'then insert the code here
End Sub

Hope this helps.

Regards,

OssieMac

"Troubled User" wrote:

I have multiple Checkbox controls on a VBForm and have a macro that evaluates
each one and if true, prints a corresponding range from the sheet. I need to
change this so that all of the different print jobs are combined into one job
and one job is sent to the printer (or in this case a image writer).
Additionally, I need to be able to set page numbers starting with the first
true case and then counting through the entire document, ie not reseting on
the second range.

Thanks for your help.