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

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

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



Judy Ward wrote:

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

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
"Judy Ward" wrote:

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

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

"Judy Ward" wrote:

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