Multiple Sheet Print Range

[lanrcdd]

Is there a way to set the same print range for a group of worksheets without
having to set it for each sheet individually?

 

[Gord Dibben]

Try this macro

Sub Set_Print_Range()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$E$20"
Next
End Sub

Select the sheets first then run the macro.


Gord Dibben MS Excel MVP

 

[lanrcdd]

Gord:
Thank You, this worked perfectly!!! Now here's a real challenge that relates
to this same work book. Is it possible to do the following: I have a workbook
with 25 worksheets and each worksheet has a "Page 1" print range (A1:K41) and
a "Page 2" Print Range (M9:V41). Is it possible to setup a macro that will
setup to print the entire workbook in the following order: ws1-print range 1,
ws1-print range 2, ws2-print range 1, ws2-print range 2, ws3-print range 1,
ws3-print range 2, etc.?

Bruce

 

[Gord Dibben]

You could try this macro.

Note: to set things like margins, headers, footers etc. group the sheets and do
those steps before running the macro.

Or record a macro whilst doing those steps and incorporate into the
Set_Print_Range macro.

Sub Set_Print_Range()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
With ActiveSheet
.PageSetup.PrintArea = "$A$1:$K$41"
.PrintOut
.PageSetup.PrintArea = "$M$9:$V$41"
.PrintOut
End With
Next ws
End Sub


Gord

 

[lanrcdd]

Thanks again, works like a charm.

You could try this macro.

Note: to set things like margins, headers, footers etc. group the sheets and do
those steps before running the macro.

Or record a macro whilst doing those steps and incorporate into the
Set_Print_Range macro.

Sub Set_Print_Range()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
With ActiveSheet
.PageSetup.PrintArea = "$A$1:$K$41"
.PrintOut
.PageSetup.PrintArea = "$M$9:$V$41"
.PrintOut
End With
Next ws
End Sub


Gord