These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Insert headers and footers

This example macro inserts a header/footer in every worksheet in the active workbook. It also inserts the complete path to the workbook.

Sub InsertHeaderFooter()
' inserts the same header/footer in all worksheets
Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        Application.StatusBar = "Changing header/footer in " & ws.Name
        With ws.PageSetup
            .LeftHeader = "Company name"
            .CenterHeader = "Page &P of &N"
            .RightHeader = "Printed &D &T"
            .LeftFooter = "Path : " & ActiveWorkbook.Path
            .CenterFooter = "Workbook name &F"
            .RightFooter = "Sheet name &A"
        End With
    Next ws
    Set ws = Nothing
    Application.StatusBar = False
End Sub

If you don't want to apply the same header/footer to all worksheets in the active workbook, you can do something like this:

Sub InsertHeaderFooter2()
' inserts the same header/footer in some worksheets
Dim ws As Worksheet, i As Long
    Application.ScreenUpdating = False
    i = 0
    For Each ws In ActiveWorkbook.Worksheets
        i = i + 1
        If i >= 3 Then ' change only the 3rd to the last worksheet
            Application.StatusBar = "Changing header/footer in " & ws.Name
            With ws.PageSetup
                .LeftHeader = "Company name"
                .CenterHeader = "Page &P of &N"
                .RightHeader = "Printed &D &T"
                .LeftFooter = "Path : " & ActiveWorkbook.Path
                .CenterFooter = "Workbook name &F"
                .RightFooter = "Sheet name &A"
            End With
        End If
    Next ws
    Set ws = Nothing
    Application.StatusBar = False
End Sub

Document last updated 2004-06-03 11:04:33