How to add a header and footer to an Excel worksheet with VBA automatically.

Watch this video on YouTube.

Here’s the complete VBA code to add and remove the header and footer:

Sub addCustomHeader()
Dim customHeader As String
customHeader = InputBox(“enter your header text here”, “custom Header”)

With ActiveSheet.PageSetup
.LeftHeader = “”
.CenterHeader = customHeader
.RightHeader = “”
.LeftFooter = “”
.CenterFooter = “page &P of &N”
.RightFooter = “&D”
End With

End Sub

Sub removeCustomHeader()
With ActiveSheet.PageSetup
.LeftHeader = “”
.CenterHeader = “”
.RightHeader = “”
.LeftFooter = “”
.CenterFooter = “”
.RightFooter = “”
End With
End Sub