PDA

View Full Version : [SOLVED] Set multiple Print Areas



malik641
09-26-2005, 10:18 AM
I have some code that prints 3 seperate sections of a worksheet (all sections are from the same worksheet). I know that this code can be reduced, and I was wondering if anybody can help me do so.

Here's the code:


Sub Print_CP055()
Application.ScreenUpdating = False
'First Page
'Set Print Area
ActiveSheet.PageSetup.PrintArea = "Freezer_CP055_1"
'Set Page Setup
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintQuality = 600
End With
'Print
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, Collate:=True
'Second Page
'Set Print Area
ActiveSheet.PageSetup.PrintArea = "Freezer_CP055_2"
'Print
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, Collate:=True
'Third Page
'Set Print Area
ActiveSheet.PageSetup.PrintArea = "Freezer_CP055_3"
'Print
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, Collate:=True
'Clear Print Area
ActiveSheet.PageSetup.PrintArea = ""
Application.ScreenUpdating = True
End Sub




The "Freezer_CP055_1,2,3" are all named ranges in my workbook.

How can I set all 3 ranges to three seperate print areas and have it become 3 seperate pages where I can simply print from pages 1 to 3?

Thanks in advance :hi:

vonpookie
09-26-2005, 11:41 AM
I don't know about setting it to 3 different pages, but I can shorten your code a bit. Does this work for you?


Sub Print_CP055()
Dim i As Integer, PrntArea As Variant
Application.ScreenUpdating = False
'Change page settings
With ActiveSheet.PageSetup
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintQuality = 600
End With
'variable with the names of the 3 ranges
PrntArea = Array("Freezer_CP055_1", "Freezer_CP055_2", "Freezer_CP055_3")
'loops through the number of items in the PrntArea variable
For i = 0 To UBound(PrntArea)
'sets the print area to the next item in the PrntArea _
variable each time it loops--should have 3 different _
printouts, one for each range in PrntArea
ActiveSheet.PageSetup.PrintArea = PrntArea(i)
MsgBox "ok"
'Print
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, Collate:=True
Next i
Application.ScreenUpdating = True
End Sub

malik641
09-29-2005, 01:36 PM
Vonpookie,
Thanks a lot! Code's workin' great :thumb

Mark it solved!