View Full Version : Loop Format (Not Looping)

07-22-2018, 12:20 PM

I am trying to use the following loop to format all the sheets in the workbook. It seems to only be applying to the active worksheet. Please tell me what I am doing wrong.

Thank you!

Sub PL_Format()

Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$7"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintComments = xlPrintNoComments
.PrintQuality = 360
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
Next I

End Sub

07-22-2018, 04:09 PM
Welcome to the forums

Take a minute to read the FAQ's, including the part about using CODE tags

Anyway, ...

It's applying it to just the active sheet because that's what you told it to do

With ActiveSheet.PageSetup

You probably wanted

With ActiveWorkbook.Worksheets(I).PageSetup