Faster Way to Execute Code
 

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.
--
Ken Hudson

Faster Way to Execute Code
 

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.

--
Don Guillett
SalesAid Software

"Ken Hudson" wrote in message
...
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.
--
Ken Hudson

Faster Way to Execute Code
 

See

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

In article ,
Ken Hudson wrote:

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.

Faster Way to Execute Code
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Don Guillett" wrote in message ...
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.

--
Don Guillett
SalesAid Software

"Ken Hudson" wrote in message
...
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.
--
Ken Hudson

Faster Way to Execute Code
 

Hi J.E

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"JE McGimpsey" wrote in message ...
See

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

In article ,
Ken Hudson wrote:

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.

Faster Way to Execute Code
 

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.
--
Ken Hudson


"JE McGimpsey" wrote:

See

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

In article ,
Ken Hudson wrote:

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.

Faster Way to Execute Code
 

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
--


Gary


"Ken Hudson" wrote in message
...
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.
--
Ken Hudson


"JE McGimpsey" wrote:

See

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

In article ,
Ken Hudson wrote:

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.