Page Setup Macro

Hi There,

I recorded a simple page set up Macro (see below), but it takes forever
to run, like at least a minute. Is that because of the "application
inches to points...?"
If anyone knows how I can achieve this page setup in a shorter time via
a macro would appreciate it.


Sub Page_setup_Final_matched()
'
' Page_setup_Final_matched

Range("C2").Select
ActiveWindow.FreezePanes = True
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A:$R"
With ActiveSheet.PageSetup

.CenterHeader = "&""Arial,Bold""&12PHAC matched to Data"

.LeftFooter = "&""Arial,Bold Italic""&8printed on &D at &T"
.CenterFooter = "&""Arial,Bold""Page &P of &N"
.RightFooter = "&""Arial,Bold Italic""&8File: &F, Tab:&A"
.LeftMargin = Application.InchesToPoints(0.354330708661417)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.TopMargin = Application.InchesToPoints(0.551181102362205)
.BottomMargin = Application.InchesToPoints(0.54)
.HeaderMargin = Application.InchesToPoints(0.275590551181102)
.FooterMargin = Application.InchesToPoints(0.25)

.Orientation = xlLandscape

.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic

.Zoom = 80
.PrintErrors = xlPrintErrorsDisplayed
End With
Range("C10").Select
End Sub




*** Sent via Developersdex http://www.developersdex.com ***

Darin,

Changing PageSetUp is notoriously slow.
Only change the settings that need changing.
If the default settings are close enough, don't change them.
Set the display of pagebreaks to false.
There are 72 points to the inch.
Possibly...
'-----------------------
Sub Page_setup_Final_matched()
Range("C2").Select
ActiveWindow.FreezePanes = True
ActiveSheet.DisplayPageBreaks = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintArea = "$A:$R"
.CenterHeader = "&""Arial,Bold""&12PHAC matched to Data"
.LeftFooter = "&""Arial,Bold Italic""&8printed on &D at &T"
.CenterFooter = "&""Arial,Bold""Page &P of &N"
.RightFooter = "&""Arial,Bold Italic""&8File: &F, Tab:&A"
.LeftMargin = 25
.RightMargin = 25
.TopMargin = 40
.BottomMargin = 39
.HeaderMargin = 20
.FooterMargin = 18
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = 80
End With
Range("C10").Select
End Sub
'---------------------------------

Regards,
Jim Cone
San Francisco, USA




"Darin Kramer" <darin_kramer@hotmail.com> wrote in message
news:%231kbtikdFHA.3048@TK2MSFTNGP12.phx.gbl...
Hi There,
I recorded a simple page set up Macro (see below), but it takes forever
to run, like at least a minute. Is that because of the "application
inches to points...?"
If anyone knows how I can achieve this page setup in a shorter time via
a macro would appreciate it.

Sub Page_setup_Final_matched()
' Page_setup_Final_matched
Range("C2").Select
ActiveWindow.FreezePanes = True
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A:$R"
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""&12PHAC matched to Data"

.LeftFooter = "&""Arial,Bold Italic""&8printed on &D at &T"
.CenterFooter = "&""Arial,Bold""Page &P of &N"
.RightFooter = "&""Arial,Bold Italic""&8File: &F, Tab:&A"
.LeftMargin = Application.InchesToPoints(0.354330708661417)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.TopMargin = Application.InchesToPoints(0.551181102362205)
.BottomMargin = Application.InchesToPoints(0.54)
.HeaderMargin = Application.InchesToPoints(0.275590551181102)
.FooterMargin = Application.InchesToPoints(0.25)
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Zoom = 80
.PrintErrors = xlPrintErrorsDisplayed
End With
Range("C10").Select
End Sub

Here's a thread that addresses the same problem:

http://groups.google.com/groups?thre...TNGP12.phx.gbl

Two tips are included--only adjust the parms you need to adjust.

Use the John Green version (XLM works faster than VBA in this situation).

(posted by Tom Ogilvy and Ron de Bruin)

Darin Kramer wrote:
>
> Hi There,
>
> I recorded a simple page set up Macro (see below), but it takes forever
> to run, like at least a minute. Is that because of the "application
> inches to points...?"
> If anyone knows how I can achieve this page setup in a shorter time via
> a macro would appreciate it.
>
> Sub Page_setup_Final_matched()
> '
> ' Page_setup_Final_matched
>
> Range("C2").Select
> ActiveWindow.FreezePanes = True
> With ActiveSheet.PageSetup
> .PrintTitleRows = "$1:$1"
> .PrintTitleColumns = ""
> End With
> ActiveSheet.PageSetup.PrintArea = "$A:$R"
> With ActiveSheet.PageSetup
>
> .CenterHeader = "&""Arial,Bold""&12PHAC matched to Data"
>
> .LeftFooter = "&""Arial,Bold Italic""&8printed on &D at &T"
> .CenterFooter = "&""Arial,Bold""Page &P of &N"
> .RightFooter = "&""Arial,Bold Italic""&8File: &F, Tab:&A"
> .LeftMargin = Application.InchesToPoints(0.354330708661417)
> .RightMargin = Application.InchesToPoints(0.354330708661417)
> .TopMargin = Application.InchesToPoints(0.551181102362205)
> .BottomMargin = Application.InchesToPoints(0.54)
> .HeaderMargin = Application.InchesToPoints(0.275590551181102)
> .FooterMargin = Application.InchesToPoints(0.25)
>
> .Orientation = xlLandscape
>
> .PaperSize = xlPaperA4
> .FirstPageNumber = xlAutomatic
>
> .Zoom = 80
> .PrintErrors = xlPrintErrorsDisplayed
> End With
> Range("C10").Select
> End Sub
>
> *** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson