Here's a thread that addresses the same problem:

http://groups.google.com/groups?thre...GP12.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