Set Print Area Multiple Sheet

  • Thread starter Thread starter TonyB
  • Start date Start date
T

TonyB

How do i set a print area for a section in a multiple
sheet without going through each sheet individulally
 
Tony

Unfortunately setting the print area on grouped sheets is not an option
without using VBA.

Code from Bob Flanagan for setting same print area on grouped sheets.

Set the print area on ActiveSheet then Group the sheets and run the macro.

Note: after print area is set you will most likely have to re-group to set
margins.

Sub Set_Print_Area_On_All_Selected_Sheets()
Dim tempS As String, oSheets As Object
Dim curSheet As Worksheet, oSheet As Worksheet
Dim iResponse

Application.ScreenUpdating = False
iResponse = MsgBox(prompt:= _
"Select OK to set the print area on all " & _
"selected sheets the same as the print " & _
"area on this sheet. If you have not selected " & _
"any sheets, then all worksheets will be set.", _
Buttons:=vbOKCancel)
If iResponse = vbCancel Then End

'store info
tempS = ActiveSheet.PageSetup.PrintArea
'set an object variable to refer to the sheets to be set
If ActiveWindow.SelectedSheets.Count = 1 Then
'if no sheets selected, select all worksheets
Set oSheets = ActiveWorkbook.Worksheets
Else
'set variable to select sheets
Set oSheets = ActiveWindow.SelectedSheets
End If

'store the current sheet and then rotate through each
'sheet and set the print area
Set curSheet = ActiveSheet
For Each oSheet In oSheets
If oSheet.Type = xlWorksheet Then
'set print area only if a worksheet
oSheet.PageSetup.PrintArea = tempS
End If
Next

'return to the original worksheet

curSheet.Select
MsgBox "All print areas on the selected sheets have " & _
"been set to the same as this sheet."
End Sub

Gord Dibben Excel MVP
 
Back
Top