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 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-05-05 13:15:19 CET):

Re: What about creation Date (part2)
You can insert a static date/time like this:
ws.PageSetup.RightHeader = "Creation Date/Time: " & Format(Now, "yyyy-mm-dd hh:mm:ss")

ALO from Shreveport, Louisiana, USA wrote (2006-05-04 16:41:55 CET):

What about creation Date (part2)
I'm trying to figure out how to insert the creation date as a header of a chart and spreadsheet. It can really help let me know when I started working on the project and its duration.

Ole P. from Norway wrote (2006-04-20 08:33:23 CET):

Re: Change the start number of a Sheet by hand
Use the menu File, Page Setup...
In the Page tab you can change the "First page number" setting from Auto to your desired page number.

TanDUNG from Thailand wrote (2006-04-20 07:32:05 CET):

Change the start number of a Sheet by hand
How I can input the start number of page for every Sheet that want to print out ?

Ole P. from Norway wrote (2004-06-07 09:51:16 CET):

Re: Prevent User Changes
See the example "Apply a default header/footer using Custom Views" in the "Worksheets" section on the menu to the left.

Charles S. from Chicago, IL wrote (2004-06-04 17:26:02 CET):

Prevent User Changes
Is there any way to prevent users from changing the header / footer on worksheets I create? I've tried your code, but if they choose View-Header and Footer... and enter different information in the header / footer, it overwrites the VBA macro.

I can't find a protection setting that works on headers/footers.

Ole P. from Norway wrote (2004-06-03 11:06:00 CET):

Re: Apply Header Format to Sheets3 to Last Sheet
See the updated example :-)

Bill from Illinois wrote (2004-06-03 05:00:59 CET):

Apply Header Format to Sheets3 to Last Sheet
How do I code this macro so it only applies to the 3rd to last sheet?

Ole P. from Norway wrote (2004-04-15 00:18:46 CET):

Re: What about creation date?
You can use something like this to retrieve the creation date and time for the active workbook:
ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date").Value

This will return the creation date only for the active workbook:
Left$(ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date").Value,10)

E.g.:
ActiveSheet.PageSetup.RightHeader = "Created " & ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date").Value

Kresimir from Osijek, Croatia wrote (2004-04-14 09:19:32 CET):

What about creation date?
This is a nice example, but could you tell me how I can put in header date of creation? Because I want to make an excel report where in header will be displayed date of file creation.