Speed of PageSetup

[John G]

Dear all,

I have a list of data that creates 100 or so workbooks. The VBA code runs
very fast apart from the pagesetup stage which pauses for a few seconds. Is
their a way to speed this up.

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PrintGridlines = True
.CenterFooter = "Page &P of &N"
.RightFooter = "Grand Total Summary "
.CenterHorizontally = True
.LeftMargin = Application.InchesToPoints(0.15748031496063)
.RightMargin = Application.InchesToPoints(0.118110236220472)
.TopMargin = Application.InchesToPoints(0.78740157480315)
.BottomMargin = Application.InchesToPoints(0.78740157480315)
.PrintTitleRows = "$1:$1"
End With

Thanks,

John G

[Nick Hodge]

John

Code looks fine to me...page set-up is notoriously slow, unfortunately. I
don't think it's totally XL's fault, but suspect it's having to read printer
settings, etc.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_...@lineone.net

"John G" <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...

[SimPoh]

Hi John,

For every PageSetup property you run, time is required. If you could take
away some standard property like the margins, your code will run faster.

Regards
Sim Poh

John G <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...

[Dave Peterson]

John Green has posted this in the past. It uses the old xl4 macro language that
works more quickly than the VBA statements.

You should be able to pick apart the pieces that you need.

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 = 600
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = False
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

--

Dave Peterson
ec3...@msn.com

[Tom Ogilvy]

Why not set up a single worksheet including the pagesetup. Then copy that
sheet each time.

Regards,
Tom Ogilvy

John G <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...

[Vasant Nanavati]

Hi John:

Try something like:

ExecuteExcel4Macro "PAGE.SETUP(" & Chr(34) & "" & Chr(34) & "," & Chr(34) &
"&CPage &P of &N&RGrand Total Summary" & Chr(34) &
",.15748031496063,.0118110236220472,.78740157480315,.78740157480315,FALSE,TR
UE,TRUE,FALSE,2,,100,,1,,,,,,)"

It's unbelievably fast compared to VBA. Watch for line wrapping--it's all
one line.

Regards,

Vasant.

John G <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...

[Vasant Nanavati]

You beat me to it again, Dave! Now who can't get a word in edgewise? :)

Dave Peterson <ec3...@msn.com> wrote in message
news:3BC0714A...@msn.com...

[Dana DeLouis]

You may want to also consider using a workbook template.
Have most of your work done in this workbook, and save it as a template.
If you are importing data, Excel 2000 can import directly into this new
workbook.
That was a nice added feature in Excel 2000 because of this.

As a side note, I have noticed that different printers work at different
speeds which I believe is based on their Printer Driver.
But I have no references for that. Just an observation.
--
Dana DeLouis Windows Me & Office XP

"John G" <jo...@hotmail.com> wrote in message
news:9pphrr$pgp$1...@news6.svr.pol.co.uk...