Tip Printed from ExcelTip.com
Insert headers and footers using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions



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