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 *** |
Page Setup Macro
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" wrote in message ... 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 |
Page Setup Macro
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 |
All times are GMT +1. The time now is 11:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com