Speeding up when setting 'Page Setup'
Usually, a page setup is performed using a
PageSetup method.
However, it takes time considerably when performing page setup methods of many worksheets on a workbook.
So I would introduce the method of performing a page setup at high speed with
Application.Dialogs.
The following code makes a high-speed page setup possible by displaying a dialog for page setting, then close the dialogue by Sendkeys method after appropriate arguments are filled.
Example : Place the following in the standard module.
Option Explicit
Sub TestingPageSetUp()
'// Speed up the page setting all worksheets in a workbook
Dim sh As Worksheet
For Each sh In Worksheets
sh.Activate
Application.SendKeys "{ENTER}", False
Application.Dialogs(xlDialogPageSetup).Show _
Arg1:="&L&""Arial,Italic""&9LEFT_HEADER" & _
"&C&""Arial,Bold""&9CENTER_HEADER" & _
"&R&""Arial""&9RIGHT_HEADER", _
Arg2:="&L&""Arial""&9LEFT_FOOTER" & _
"&C&""Arial""&9CENTER_FOOTER" & _
"&R&""Arial""&9RIGHT_FOOTER", _
Arg3:=0.5, _
Arg4:=0.5, _
Arg5:=0.5, _
Arg6:=0.5, _
Arg12:=xlPaperLetter
Next
End Sub
|
Remarks
The arguments which can be used in Application.Dialogs (xlDialogPageSetup) are as follows.
- Arg1 Header
- Arg2 Footer
- Arg3 Left Margin
- Arg4 Right Margin
- Arg5 Top Margin
- Arg6 Bottom Margin
- Arg7 Row and column headings
- Arg8 Gridlines
- Arg9 Center on page - Horizontally
- Arg10 Center on page - Vertically
- Arg11 Orientation
- Arg12 Paper size
- Arg13 Adjust to: x % normal size
- Arg14 First page number
- Arg15 Page order
- Arg16 Black and white
- Arg17 Print quality
- Arg18 Header Margin
- Arg19 Footer Margin
- Arg20 Comments
- Arg21 Draft quality
The arguments for Arg1(Header) and Arg2(Footer) would be a little complicated.
You can set up the
Font and
Font style as above. With regards to
Font size, sorry I have no idea about that. If you've known about it or will find, please kindly let me know.
Please note, the string "9" you can see in the Arg1 and Arg2 lines are dummy strings. It doesn't matter if it's 9, 1, A, B or just a space, but it's necessary for setting.
About 12th argument(Agr12) -
PaperSize- in the above code, for example I set it as letter size.
PaperSize Property
- XlPaperSize can be one of these XlPaperSize constants.
- xlPaper11x17. 11 in. x 17 in.
- xlPaperA4. A4 (210 mm x 297 mm)
- xlPaperA5. A5 (148 mm x 210 mm)
- xlPaperB5. B5 (148 mm x 210 mm)
- xlPaperDsheet. D size sheet
- xlPaperEnvelope11. Envelope #11 (4-1/2 in. x 10-3/8 in.)
- xlPaperEnvelope14. Envelope #14 (5 in. x 11-1/2 in.)
- xlPaperEnvelopeB4. Envelope B4 (250 mm x 353 mm)
- xlPaperEnvelopeB6. Envelope B6 (176 mm x 125 mm)
- xlPaperEnvelopeC4. Envelope C4 (229 mm x 324 mm)
- xlPaperEnvelopeC6. Envelope C6 (114 mm x 162 mm)
- xlPaperEnvelopeDL. Envelope DL (110 mm x 220 mm)
- xlPaperEnvelopeMonarch. Envelope Monarch (3-7/8 in. x 7-1/2 in.)
- xlPaperEsheet. E size sheet
- xlPaperFanfoldLegalGerman. German Legal Fanfold (8-1/2 in. x 13 in.)
- xlPaperFanfoldUS. U.S. Standard Fanfold (14-7/8 in. x 11 in.)
- xlPaperLedger. Ledger (17 in. x 11 in.)
- xlPaperLetter. Letter (8-1/2 in. x 11 in.)
- xlPaperNote. Note (8-1/2 in. x 11 in.)
- xlPaperStatement. Statement (5-1/2 in. x 8-1/2 in.)
- xlPaperUser. User-defined
- xlPaper10x14. 10 in. x 14 in.
- xlPaperA3. A3 (297 mm x 420 mm)
- xlPaperA4Small. A4 Small (210 mm x 297 mm)
- xlPaperB4. B4 (250 mm x 354 mm)
- xlPaperCsheet. C size sheet
- xlPaperEnvelope10. Envelope #10 (4-1/8 in. x 9-1/2 in.)
- xlPaperEnvelope12. Envelope #12 (4-1/2 in. x 11 in.)
- xlPaperEnvelope9. Envelope #9 (3-7/8 in. x 8-7/8 in.)
- xlPaperEnvelopeB5. Envelope B5 (176 mm x 250 mm)
- xlPaperEnvelopeC3. Envelope C3 (324 mm x 458 mm)
- xlPaperEnvelopeC5. Envelope C5 (162 mm x 229 mm)
- xlPaperEnvelopeC65. Envelope C65 (114 mm x 229 mm)
- xlPaperEnvelopeItaly. Envelope (110 mm x 230 mm)
- xlPaperEnvelopePersonal. Envelope (3-5/8 in. x 6-1/2 in.)
- xlPaperExecutive. Executive (7-1/2 in. x 10-1/2 in.)
- xlPaperFanfoldStdGerman. German Legal Fanfold (8-1/2 in. x 13 in.)
- xlPaperFolio. Folio (8-1/2 in. x 13 in.)
- xlPaperLegal. Legal (8-1/2 in. x 14 in.)
- xlPaperLetterSmall. Letter Small (8-1/2 in. x 11 in.)
- xlPaperQuarto. Quarto (215 mm x 275 mm)
- xlPaperTabloid. Tabloid (11 in. x 17 in.)
- Note Some printers may not support all of these paper sizes. -
Although it is an old function but I would mention that the "Application.ExecuteExcel4Macro "PAGE.SETUP" is also a good way for high speed page setup.
