Excel

Faster Page Setup

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Justinlabenne

Description:

Incorporates a method to improve speed when copying page setup settings to all sheets in a workbook. Also shows a way to include document properties in a header or footer 

Discussion:

If you are using the same page setup settings for all the sheets in a workbook, looping through a lot of sheets and applying lots of settings can become quite slow and tedious. This method avoids the loop and simulates the manual method of a quick page setup. This code also makes use of incorporating some document properties into the headers/footers, and uses an old xlm macro to obtain the number of pages to print per sheet. *NOTE: Microsoft still supports xlm macros but may not in the future. 

Code:

instructions for use

			

Option Explicit Sub PageSetupEverySheetFaster() ' --------------------------------------------------------------------------- ' Add some document property info to the footers: Dim szLastSaveTime As String szLastSaveTime = ThisWorkbook.BuiltinDocumentProperties("Last Save Time") Dim szHyperLink As String szHyperLink = ThisWorkbook.BuiltinDocumentProperties("HyperLink Base") ' --------------------------------------------------------------------------- ' --------------------------------------------------------------------------- ' We use an old xlm macro to get the number of pages we will print from the ' the active sheet Dim lPageNum As Long lPageNum = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") ' --------------------------------------------------------------------------- ' --------------------------------------------------------------------------- ' Proceed with a basic setup: DO the first sheet only here: With Worksheets(1).PageSetup .LeftFooter = "Last Saved: " & _ Format(szLastSaveTime, "mm-dd-yy hh:mm:ss") .RightFooter = szHyperLink .RightHeader = "&D" .LeftHeader = "Page &P of " & lPageNum .Orientation = xlLandscape .CenterHeader = "MAIN HEADER" End With ' --------------------------------------------------------------------------- ' Select all worksheets at once: Faster than looping through! Worksheets.Select ' --------------------------------------------------------------------------- ' Simulate key presses for "File > PageSetup > OK" ' This copies the PageSetup info to all the other sheets SendKeys "%f", True SendKeys "u" SendKeys "{ENTER}", True ' --------------------------------------------------------------------------- Worksheets(1).Select End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Press Alt + F11 to open the Visual Basic Editor (VBE)
  4. Select INSERT > MODULE from the menubar
  5. Paste code into the right pane
  6. Press Alt+Q to return to Excel
  7. Save workbook before any other changes
 

Test the code:

  1. Go to TOOLS > MACRO > MACROS
  2. When the dialog appears, select (PageSetupEverySheetFaster)
  3. Press Run
 

Sample File:

SpeedySetup.zip 11.44KB 

Approved by mdmackillop


This entry has been viewed 347 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express