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

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo