Printing off many areas within a worksheet

[Gary Thomson]

Hi,

I am looking for a macro that will print a whole set of
pages within the one worksheet for me, each of which I
will set using PrintArea->Set Print Area.

Let me start by considering one school (there are 42 in
total). I need to print off a budget statement for this
school, which will consist of 3 distinct "pages".

These pages lie horizontally tiled in a worksheet:

The first "page" is a 60 row by 6 column grid, contained
in B2:G61.

The second "page" is a 34 row by 7 column grid, contained
in J235.

The third "page" is a 24 row by 7 column grid, contained
in S2:Y25.

I have written a macro (see below) which sets the print
area for each of these ranges, one at a time, then prints
off each page, with the only difference (in the print
settings) being that page 1 is both horizontally and
vertically centred, but pages 2 and 3 are just
horizontally centred.

This is all very well, but there are going to be 42 sets
of these budget statements within this worksheet (the next
having its first page in the range B64:G123, second page
in the range J6497, third page in the range S64:Y87,
then the next having its first page in the range
B126:G185, etc).

(i.e. I have a 42 by 3 grid of "statements" in one
worksheet).

Therefore is there a Macro I can write (i.e. for ...next
or "from 1 to 42 do" kind of thing) that will print off
all of these sheets (by first setting the print area for
these sheets)???

Range("B2:G61").Select
ActiveSheet.PageSetup.PrintArea = "$B$2:$G$61"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints
(0.826771653543307)
.RightMargin = Application.InchesToPoints
(0.708661417322835)
.TopMargin = Application.InchesToPoints
(0.826771653543307)
.BottomMargin = Application.InchesToPoints
(0.748031496062992)
.HeaderMargin = Application.InchesToPoints
(0.275590551181102)
.FooterMargin = Application.InchesToPoints
(0.275590551181102)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Range("J235").Select
ActiveSheet.PageSetup.PrintArea = "$J$2:$P$35"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints
(0.826771653543307)
.RightMargin = Application.InchesToPoints
(0.708661417322835)
.TopMargin = Application.InchesToPoints
(0.826771653543307)
.BottomMargin = Application.InchesToPoints
(0.748031496062992)
.HeaderMargin = Application.InchesToPoints
(0.275590551181102)
.FooterMargin = Application.InchesToPoints
(0.275590551181102)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Range("S2:Y25").Select
ActiveSheet.PageSetup.PrintArea = "$S$2:$Y$25"
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Range("B2:G2").Select

 

[Tom Ogilvy]

Sub bldRange()
Set rng = Range("B2:G61,J235,S2:Y25")

Set rng1 = Range("B64:G123,J6497,S64:Y87")

Set rng3 = Range("B126:G185")
For i = 0 To 42
j = 0
For Each ar In rng.Areas
j = j + 1
ActiveSheet.PrintArea = "'" & Activesheet.Name & "'!" & ar.Address
if j = 1 then
' make print settings for first sheet
else
' make print settings for 2nd and 3rd sheet
End if
ActiveSheet.Printout
Next
End Sub
..

 

[Greg Koppel]

If the ranges are contiguous and always start in the same columns, you can
do something like

ActiveSheet.PageSetup.PrintArea = =Range("B1").End(xlDown).CurrentRegion

HTH, Greg