All pages to open in page break mode

[J.W. Aldridge]

Hi.

I need to open a workbook and set the print settings for a range of
sheets in a workbook.

Sheet range - sheet 2 : sheet 6
Open sheet range in page break preview
Print range - A1:L43
type - landscape
1 page only.

(do not print - just adjust settings, and leave in page break preview)

 

[Mark Driscol]

This assumes the workbook has at least six worksheets in it already.

Option Explicit

Sub SetPageSetup()
Dim i As Long
Dim wksCurrentSheet

Set wksCurrentSheet = ActiveSheet

For i = 2 To 6
With Worksheets(i)
.Select
ActiveWindow.View = xlPageBreakPreview
With .PageSetup
.PrintArea = "A1:L43"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End With
Next i

wksCurrentSheet.Select
End Sub


Mark

 

[J.W. Aldridge]

Everything went fine. Except one variable....

Sheets 2 to 6.
Sheet names may actually vary.
Any way to change that line to a range instead?
sheet range (sheetx:sheety)



Sub SetPageSetup()

 

[Dave Peterson]

If you want all the worksheets:
For i = 1 To worksheets.count

if you want to avoid the left most:
For i = 2 To worksheets.count

 

[Mark Driscol]

If you want to select the sheets to use, you can do the following. I
would avoid, if possible, using the names of your sheets in the code,
because if the sheet names change, you then have to change your code.


Option Explicit

Sub SetPageSetup()
Dim wks As Worksheet
Dim wksCurrentSheet As Worksheet

Set wksCurrentSheet = ActiveSheet

For Each wks In ActiveWindow.SelectedSheets
With wks
.Select
ActiveWindow.View = xlPageBreakPreview
With .PageSetup
.PrintArea = "A1:L43"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End With
Next wks


wksCurrentSheet.Select
End Sub


Mark