Faster Way to Execute Code

[Guest]

Hi,
I have a couple of macros in which I create many (100-200) worksheets in one
workbook. As part of the formatting I have a loop with code like that shown
below to establish the page set up in each sheet. When I run this macro, it
looks to me like this part of the code inceases the macro run time
considerably. Is there any way to shorten this time?

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$3"
.PrintArea = "$A$1:$I" & NumRowsActSheet
.CenterHeader = "&""Arial,Bold""&12F820 Report for " & ActiveSheet.Name
.LeftFooter = "&P of &N"
.RightFooter = "&D &T"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.PrintGridlines = True
.CenterHorizontally = True
.Orientation = xlLandscape
.Zoom = 100
End With

TIA.

 

[Don Guillett]

Page setup macros are slow. Only change what is necessary.
OR......
Make a template with the formatting desired and COPY the template instead of
ADDING a sheet.

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/pagesetup.html

 

[Ron de Bruin]

For information about a template see
http://www.rondebruin.nl/sheettemplate.htm


You can also use this (faster)
Saved from a old post

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

 

[Ron de Bruin]

Hi J.E

I always forget you have it on your site.
I try to remember it the next time

 

[Guest]

This looks great.
One follow-up question:
How do I "pass" the center header info? The sub is expecting string data and
I want to pass: "&""Arial,Bold""&12F820 Report as of " & Dateholder & " for
FCP " & FCP. This includes font set ups and variables.

CenterHeader:="&""Arial,Bold""&12F820 Report as of " & Dateholder & " for
FCP " & FCP

doesn't work.

 

[Gary Keramidas]

this is all i ever use, but i don't know what dateholder is and what fcp is.

With ws.PageSetup
..CenterHeader = "&B&12" & "F820 Report as of " & dateHolder & " for FCP" & "
FCP"
End With


try removing the colon after centerheader