Macro to change worksheet page settings VERY SLOW.
 

I have a macro that I want to change a worksheet page settings.

However this runs very slow.

Are there any tips on how to speed this up.

Also can mutiple sheets be changed at the same time with the same settings
e.g page layout, margins, rows to repeat at top etc.

Macro to change worksheet page settings VERY SLOW.
 

"Nexus" wrote in message
...
I have a macro that I want to change a worksheet page settings.

However this runs very slow.

Are there any tips on how to speed this up.

Also can mutiple sheets be changed at the same time with the same settings
e.g page layout, margins, rows to repeat at top etc.



You might try setting ScreenUpdating to false while macro is running:

Application.ScreenUpdating = False

.... your page setting code

Application .ScreenUpdating = True

I don't know about multiple sheet settings.


--
---------------------------------------------------------------
Michael J. Strickland
Quality Services
703-560-7380
---------------------------------------------------------------

Macro to change worksheet page settings VERY SLOW.
 

To do multiple pages

grouping (which is what you would use manually) is largely not supported in
VBA, however.


If you have one sheet set up, then KeepItCool has suggested this as a way to
format multiple sheets:


sheets(array("sheet2","sheet3","sheet4")).select
sheets("sheet3").activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show


keepITcool


-------------------------

So you would set up one sheet using the xl4 approach below, then use
KeepItCool's technique for the other sheets.


Other than that you would need to loop through the sheets. You are probably
already aware that pagesetup is extremely slow. So you should only set
those attributes that you need to do because each setting is an individual
call to the slow pagesetup object.


somewhat faster is to use the xl4 macro approach posted here by John Green:


From: John Green )
Subject: Pagesetup code takes too long
Newsgroups: microsoft.public.excel.programming
View complete thread (5 articles)
Date: 1999/03/29


Macro =
"Page.Setup(,,.25,.25,.5,.25,,False,True,True,2,1, {1,1},,,,,.25,.25)"
ExecuteExcel4Macro Macro


HTH,


John Green - Excel MVP
Sydney
Australia


=================================
From: John Green )
Subject: 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


--
Regards,
Tom Ogilvy


"Nexus" wrote in message
...
I have a macro that I want to change a worksheet page settings.

However this runs very slow.

Are there any tips on how to speed this up.

Also can mutiple sheets be changed at the same time with the same settings
e.g page layout, margins, rows to repeat at top etc.

Macro to change worksheet page settings VERY SLOW.
 

It has to be done programmatically without any user intervention so this
wouldn't work.

By not applying the page setting my macro generates in 10 seconds. With it
can take over 1 min with 30 page setups.

"Tom Ogilvy" wrote in message
...
To do multiple pages

grouping (which is what you would use manually) is largely not supported
in
VBA, however.


If you have one sheet set up, then KeepItCool has suggested this as a way
to
format multiple sheets:


sheets(array("sheet2","sheet3","sheet4")).select
sheets("sheet3").activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show


keepITcool


-------------------------

So you would set up one sheet using the xl4 approach below, then use
KeepItCool's technique for the other sheets.


Other than that you would need to loop through the sheets. You are
probably
already aware that pagesetup is extremely slow. So you should only set
those attributes that you need to do because each setting is an individual
call to the slow pagesetup object.


somewhat faster is to use the xl4 macro approach posted here by John
Green:


From: John Green )
Subject: Pagesetup code takes too long
Newsgroups: microsoft.public.excel.programming
View complete thread (5 articles)
Date: 1999/03/29


Macro =
"Page.Setup(,,.25,.25,.5,.25,,False,True,True,2,1, {1,1},,,,,.25,.25)"
ExecuteExcel4Macro Macro


HTH,


John Green - Excel MVP
Sydney
Australia


=================================
From: John Green )
Subject: 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


--
Regards,
Tom Ogilvy


"Nexus" wrote in message
...
I have a macro that I want to change a worksheet page settings.

However this runs very slow.

Are there any tips on how to speed this up.

Also can mutiple sheets be changed at the same time with the same
settings
e.g page layout, margins, rows to repeat at top etc.