Slow PageSetup macro

[Judy Ward]

I need to loop through about 20 worksheets in one file and format them all
the same way. I basically just turned on the macro recorder to record the
steps it takes to format one sheet. My macro works great except that is
spends a long time on the PageSetup section.

Does anyone have any idea why the code below takes so long to run or a
suggestion for a better way to accomplish the same thing?
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
.CenterHeader = "&A"
.CenterFooter = "Page &P"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintGridlines = True
.CenterHorizontally = True
.Orientation = xlPortrait
.Zoom = 100
End With

Thank you for your help,
Judy

 

[Dave Peterson]

Are there any settings that you don't have to change?

If yes, then remove them (or comment them) in your code.

 

[Barb Reinhardt]

I wouldn't activate them. Assuming you want to do this on all sheets in the
workbook, do this

Dim myWB as Excel.workbook
Dim myWS as Excel.Worksheet

Set myWB = ThisWorkbook

for each myWS in myWB.Worksheets

With myws.PageSetup
..PrintTitleRows = "$1:$1"
..PrintTitleColumns = ""
..CenterHeader = "&A"
..CenterFooter = "Page &P"
..LeftMargin = Application.InchesToPoints(0.25)
..RightMargin = Application.InchesToPoints(0.25)
..TopMargin = Application.InchesToPoints(0.5)
..BottomMargin = Application.InchesToPoints(0.5)
..HeaderMargin = Application.InchesToPoints(0.25)
..FooterMargin = Application.InchesToPoints(0.25)
..PrintGridlines = True
..CenterHorizontally = True
..Orientation = xlPortrait
..Zoom = 100
End With

next myWS

I'm not sure if there are things that can be done globally for the other
things. I don't do a lot of work in headers and footers.

HTH,
Barb Reinhardt

 

[Barb Reinhardt]

You may want to just calculate this once outside of the loop

myVal25 = Application.InchesToPoints(0.25)
myVal50 = Application.InchesToPoints(0.50)

And do this

..leftmargin = myVal25

HTH,
Barb Reinhardt