This site will look better if you upgrade to a browser that supports web standards.

Print headers and footers on the first page only

Unlike Word, XL doesn't have separate "first page" headers and footers - it prints headers and footers on every page. To print the header or footer only on the first page, or only on subsequent pages, you'll need to use a macro. Headers and footers in XL exist in three parts each: LeftHeader, CenterHeader, RightHeader, LeftFooter, CenterFooter, and RightFooter - you'll need to change each one that is used or hidden. The macros can be either manually initiated, or run automatically when the user gives the Print command.

The following macros use the footers in each sheet and temporarily deletes the footer either on the first or subsequent pages.

Manually print different headers/footers

Put a macro like one of the ones below in a regular code module. Attach it to a button, toolbar control, or keyboard shortcut, or run it from the Tools/Macro/Macros menu. The different headers or footers can also be implemented automatically when the Print command is chosen.

Print footer on first page only (one sheet only)

    Public Sub FirstPageFooterOnly_OneSheet()
        Dim sFooter As String
        With Sheets("My Sheet")
            sFooter = .PageSetup.LeftFooter
            .PrintOut From:=1, To:=1
            .PageSetup.LeftFooter = ""
            .PrintOut From:=2
            .PageSetup.LeftFooter = sFooter
        End With
    End Sub

Print footer on all but first page (one sheet only)

    Public Sub NoFirstPageFooter_OneSheet()
        Dim sFooter As String
        With Sheets("My Sheet")
            sFooter = .PageSetup.LeftFooter
            .PageSetup.LeftFooter = ""
            .PrintOut From:=1, To:=1
            .PageSetup.LeftFooter = sFooter
            .PrintOut From:=2
        End With
    End Sub

Print footer on first page only (all sheets)

    Public Sub FirstPageFooterOnly_AllSheets()
        Dim wsSheet As Worksheet
        Dim sFooter As String
        For Each wsSheet In Worksheets
            With wsSheet
                sFooter = .PageSetup.LeftFooter
                .PrintOut From:=1, To:=1
                .PageSetup.LeftFooter = ""
                .PrintOut From:=2
                .PageSetup.LeftFooter = sFooter
            End With
        Next wsSheet
    End Sub

Print footer on all but first page (all sheets)

    Public Sub NoFirstPageFooter_AllSheets()
        Dim wsSheet As Worksheet
        Dim sFooter As String
        For Each wsSheet In Worksheets
            With wsSheet
                sFooter = .PageSetup.LeftFooter
                .PageSetup.LeftFooter = ""
                .PrintOut From:=1, To:=1
                .PageSetup.LeftFooter = sFooter
                .PrintOut From:=2
            End With
        Next wsSheet
    End Sub

Automate different headers and footers

To automate different headers and footers, put a macro like one of the ones below in the ThisWorkbook code module of your workbook. The different headers or footers can also be implemented manually using a button, keyboard shortcut, or menu command.

Automate first page footers (one sheet only)

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        Dim wsSheet As Worksheet
        Dim sFooter As String
        Cancel = False
        Application.EnableEvents = False
        For Each wsSheet In ActiveWindow.SelectedSheets
            With wsSheet
                If .Name = "My Sheet" Then
                    sFooter = .PageSetup.LeftFooter
                    .PrintOut From:=1, To:=1
                    .PageSetup.LeftFooter = ""
                    .PrintOut From:=2
                    .PageSetup.LeftFooter = sFooter
                Else
                    .PrintOut
                End If
            End With
        Next wsSheet
        Application.EnableEvents = True
    End Sub

Automate footer on all but first page (one sheet only)

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        Dim wsSheet As Worksheet
        Dim sFooter As String
        Cancel = False
        Application.EnableEvents = False
        For Each wsSheet In ActiveWindow.SelectedSheets
            With wsSheet
                If .Name = "My Sheet" Then
                    sFooter = .PageSetup.LeftFooter
                    .PageSetup.LeftFooter = ""
                    .PrintOut From:=1, To:=1
                    .PageSetup.LeftFooter = sFooter
                    .PrintOut From:=2
                Else
                    .PrintOut
                End If
            End With
        Next wsSheet
        Application.EnableEvents = True
    End Sub

Automate first page footer (all selected sheets)

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        Dim wsSheet As Worksheet
        Dim sFooter As String
        Cancel = False
        Application.EnableEvents = False
        For Each wsSheet In ActiveWindow.SelectedSheets
            With wsSheet
                sFooter = .PageSetup.LeftFooter
                .PrintOut From:=1, To:=1
                .PageSetup.LeftFooter = ""
                .PrintOut From:=2
                .PageSetup.LeftFooter = sFooter
            End With
        Next wsSheet
        Application.EnableEvents = True
    End Sub

Automate footer on all but first page (all selected sheets)

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        Dim wsSheet As Worksheet
        Dim sFooter As String
        Cancel = False
        Application.EnableEvents = False
        For Each wsSheet In ActiveWindow.SelectedSheets
            With wsSheet
                sFooter = .PageSetup.LeftFooter
                .PageSetup.LeftFooter = ""
                .PrintOut From:=1, To:=1
                .PageSetup.LeftFooter = sFooter
                .PrintOut From:=2
            End With
        Next wsSheet
        Application.EnableEvents = True
    End Sub

This page last updated Tuesday, 21 December 2004

McGimpsey & Associates

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL