View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
MikeZz MikeZz is offline
external usenet poster
 
Posts: 152
Default VBA Page Setup/Formatting EXTREMELY Slow... Help!

I have a sub that disables screen updating before it calls this routine.

Unfortunately, this colde still runs extremely show.
I can create and format 10 sheets including multiple pivot tables in a
fraction of time that it takes just to add simple headers and footers to the
same 10 sheets.

Am I doing something wrong?
Thanks!

Sub Setup_All_Headers()
Dim wb As Workbook
Dim sh As Sheets
Dim c, s

Set wb = ActiveWorkbook
c = Worksheets.Count

For s = 1 To c
Worksheets(s).Activate
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""&20&A&""Arial,Regular""&14" &
Chr(10) & "&F"
.CenterFooter = "Page &P of &N"
.RightFooter = "&D" & Chr(10) & "&T"
.PrintGridlines = True
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 3
End With
Next

End Sub