VBA Page Setup/Formatting EXTREMELY Slow... Help!

[MikeZz]

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

 

[Wood Grafing]

I've noticed this too. Even with screenupdating off, it takes a very long
time to do any page setup. It appears to me that it doesn't make all of the
changes at once, but seems to apply each change indepipendantly, as in, it
goes in, makes a change, backs out, then goes back in for the next one. Turn
screen updating on and watch when page setup is being done, you get a lot of
screen flashes...

 

[Jim Thomlinson]

Here is an old Post from Tom Ogilvy...

Each setting is executed as a separate command to the pagesetup code - so if
you are executing any commands that are not necessary (the are the default
values for instance), then remove them. Each command you remove will make it
faster.

Nonetheless, calls to page setup are time consuming.

The usual recommendation is to use the Excel 4 Macro command.

From an old post by John Green:

=================================
From: John Green ([email protected])
Subject: Re: About PageSetup..
Newsgroups: microsoft.public.excel.programming
View complete thread (10 articles)
Date: 2001-01-22 12:57:23 PST




PageSetup in VBA has always been a painfully slow process. If you can't
avoid having
to set these parameters, you can use the Excel 4 macro function, PAGE.SETUP
to carry
out most of the PageSetup operations much more quickly. The following two
macros are
almost equivalent, and should give you the clues you need to start using
PAGE.SETUP.
You can download a full description of all the Excel 4 macro functions from
Microsoft's web site:

Sub PS()
ActiveSheet.DisplayPageBreaks = False
With ActiveSheet.PageSetup
.LeftHeader = "My Company"
.CenterHeader = ""
.RightHeader = "&D / &T"
.LeftFooter = "Highly Confidential and Proprietary"
.CenterFooter = ""
.RightFooter = "Finance"
.LeftMargin = Application.InchesToPoints(0.54)
.RightMargin = Application.InchesToPoints(0.3)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.36)
.HeaderMargin = Application.InchesToPoints(0.22)
.FooterMargin = Application.InchesToPoints(0.17)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
' .PrintQuality = 600 ' does not work with all the printers
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub

Sub PS4()
head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
pLeft = 0.54
pRight = 0.3
Top = 0.4
bot = 0.36
head_margin = 0.22
foot_margin = 0.17
hdng = False
grid = False
notes = False
quality = ""
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = True
pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & pLeft & "," & pRight &
","
pSetUp = pSetUp & Top & "," & bot & "," & hdng & "," & grid & "," & h_cntr
& ","
pSetUp = pSetUp & v_cntr & "," & orient & "," & paper_size & "," & pscale
& ","
pSetUp = pSetUp & pg_num & "," & pg_order & "," & bw_cells & "," & quality
& ","
pSetUp = pSetUp & head_margin & "," & foot_margin & "," & notes & "," &
Draft & ")"

Application.ExecuteExcel4Macro pSetUp
End Sub

John Green (Excel MVP)
Sydney
Australia

=====================

See if that is faster.

 

[MikeZz]

Goes MUCH Faster, although the example doesn't do all the formating... It's
still a great setup in the right direction.

Thanks!

 

[gimme_this_gimme_that]

Why don't you create a template XLS that has 10 formatted worksheets
in it , insert your data into the template, then save as a separate
XLS?

 

[Bob Flanagan]

Mike, one way to greatly speed up changing page setup is to first check the
property being changed and only change the properties that need changing.
Checking a property is far, far faster than setting it.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel