Setup in Excel VBA

When preparing a worksheet for printing, you need to configure its page setup settings. If you’re working with an existing worksheet, you’ll typically adjust these settings directly from the sheet. However, if you’re dealing with a new worksheet that will only later be filled with data via VBA, you can programmatically define the page setup using the PageSetup object in VBA.

The following procedure demonstrates how to configure various common page setup options for a worksheet using VBA:

Sub SetupWorksheetPage()
    ThisWorkbook.Activate
    ' Set page orientation to landscape
    Worksheets("Sheet1").PageSetup.Orientation = xlLandscape
    ' Include gridlines and row/column headings in the printout
    Worksheets("Sheet1").PageSetup.PrintGridlines = True
    Worksheets("Sheet1").PageSetup.PrintHeadings = True
    ' Set scaling: disable Zoom and fit to 1 page wide and 99 pages tall
    Worksheets("Sheet1").PageSetup.Zoom = False
    Worksheets("Sheet1").PageSetup.FitToPagesWide = 1
    Worksheets("Sheet1").PageSetup.FitToPagesTall = 99
    ' Center the content horizontally on the page
    Worksheets("Sheet1").PageSetup.CenterHorizontally = True
    ' Add a left-aligned header and a centered footer with page numbering
    Worksheets("Sheet1").PageSetup.LeftHeader = _
        "This is the header"
    Worksheets("Sheet1").PageSetup.CenterFooter = _
        "Page &P of &N"
End Sub

Explanation of the Procedure:

• Orientation: This property sets the print orientation. It accepts two values: xlPortrait for vertical (default) and xlLandscape for horizontal printing.
• PrintGridlines and PrintHeadings: These properties control whether the cell gridlines and row/column headers (A, B, C… and 1, 2, 3…) are printed. Set them to True to include them in the printout, or False to omit them.
• Zoom: This controls the scaling factor for printing. You can assign a percentage (e.g., 80 for 80%) to shrink the sheet, or a value over 100 to enlarge it. Setting this to False allows you to scale the sheet based on the number of pages instead.
• FitToPagesWide and FitToPagesTall: These properties specify how many pages wide and tall the printed output should be. For example, setting FitToPagesWide = 1 and FitToPagesTall = 99 ensures the content fits on one page horizontally, while the height can span up to 99 pages.
• CenterHorizontally: Setting this to True centers the printed content horizontally on the page. Similarly, CenterVertically = True would center it vertically.
• Header and Footer settings:
  • You can use LeftHeader, CenterHeader, RightHeader for the header, and similarly LeftFooter, CenterFooter, RightFooter for the footer.
  • These text properties support dynamic placeholders such as:
    • &P: Current page number
    • &N: Total number of pages
    • &D: Current date
    • &T: Current time
    • &Z: File path
    • &F: File name
    • &A: Worksheet name

In the example above, a static header is added on the left, and the footer dynamically displays the page number and total number of pages in the center.

Tip: If a line of code in VBA is too long, you can split it into multiple lines using the underscore _ character for readability, as shown in the header and footer assignments.