Page Setup Macro runs slow

W

William G

I have the following Macro to set up a standard page setup for all my
spreadsheets. However, it seems slower than if I were to do it
manually. I have other macros that take just a second or two to
complete. Any thoughts/feedback on this issue? I am using Excel 03.


With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftFooter = "&Z&F"
.RightFooter = "&D &T"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.FitToPagesWide = 1
.FitToPagesTall = False
End With
End Sub

Thanks,
William
 
S

Sheeloo

I made a few minor changes...

Original also ran in a couple of seconds for me...

Try
Sub setMargins()
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"

ActiveSheet.PageSetup.PrintArea = ""
.LeftFooter = "&Z&F"
.RightFooter = "&D &T"
i = Application.InchesToPoints(0.5)
.LeftMargin = i
.RightMargin = i
.TopMargin = i
.BottomMargin = i
j = i = Application.InchesToPoints(0.5)
.HeaderMargin = j
.FooterMargin = j
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.ScreenUpdating = True
End Sub
 
W

William G

I updated it with your changes, still seems to be slow. I guess I'm a
bit confused as to why something with only 20 lines takes longer than
some other macros with 100s? Is it due to the Page Set up screen that
causes the 'slowness'?

Thanks,
wg
 
S

Sheeloo

I am guessing that there are lot of internal objects change when you change
the margins...
 
B

bulletproof

Sheeloo;290648 said:
I am guessing that there are lot of internal objects change when yo
change
the margins...

Hey Will;
The Page.Setup runs slow because it calls the printer driver on ever
line. I have found that you can trick the macro into running fast b
installing a phantom printer as your default. I installed a printer wit
the HP LaserJet 1100 driver and it runs 10x faster than my rea
printer.
You can also use Excel 4 macros which run considerably faster.
B
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top