Insert headers and footers
2004-06-03 Worksheets 0 625
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