VBA 'PageSetup' performance

[John]

I have a macro that creates some worksheets, then goes through them and sets
Page properties (landscape, margins...) like this:

With ActiveSheet.PageSetup
.Columns.AutoFit
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
...etc..
End With

Performance is TERRIBLE when these statements run. The rest of my macro
only a couple of seconds to complete, but the statements maniuplating the
properties for the PageSetup object run incredibly slow. What makes it
worse, I need to perform the maniuplations on each of the worksheets I
create, exponentially making the problem worse.

Is there another way to do this? Anyone know why its so darned slow? Any
ideas greatly appreciated.

J

 

[Jim Cone]

John,

Setting PageSetUp is slow, memory intensive and seems to be the worst in XL97.
The best advice is don't use it unless you have to.
It you must use it, batch all of the settings and only access PageSetUp once per
sheet.
Set DisplayPageBreaks to false before and after calling PageSetUp
Do not reference any settings unless you are changing them.
Reviewing the help file will show you that "Columns" is NOT a property of
PageSetUp.
Make the above changes and see how it works.

An alternative is to set PageSetUp on only one sheet and use that sheet as the
template for your other sheets.

Another alternative, if you are still having problems with performance is to
write the
PageSetUp code in the XL4 macro language.
It does run a little faster that way.
John Green posted a very helpful example of XL4 PageSetUp code a while back.
However, it still took me quite some time to figure it all out and get it to do
what I wanted.

Regards,

Jim Cone
San Francisco, CA

I have a macro that creates some worksheets, then goes through them and sets
Page properties (landscape, margins...) like this: