Insert headers and footers using VBA in Microsoft Excel 2010

If you want to insert certain information in the header / footer of the worksheet like the file name / file path or the current date or page number, you can do so using the below code.  If it is just one worksheet you can do it manually, but if it is multiple sheets or all sheets in the workbook which need this information to populated, you can do this using a simple vba macro / code.

This sample macro will insert a header/footer in every worksheet in the active workbook.  It will also insert the complete path to the workbook.

Option Explicit

Sub InsertHeaderFooter()

Dim wsAs Worksheet
Application.ScreenUpdating = False
 Each wsInThisWorkbook.Worksheets
                   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: &A”
                  End With
Next ws
Set ws = Nothing
Application.ScreenUpdating = True
End Sub

To copy this code to your workbook, press Alt + F11 on your keyboard. Then on the left hand side, you will see Microsoft Excel Objects.  Right click and select Insert.  Then click on Module and copy this code to the code window on the right.

 

Lets break up each part of the code –

 

We start with the usual Dim statement where we declare the variables.  In this case, we have only 1 variable – ws for the worksheet. Then we disable screen updating.

Now, in the FOR loop, we loop through each worksheet in the workbook which contains the macro. And we setup each parameter in Page Setup.  &P, &N, &D, &T, &F and &A are certain format codes which can be applied to headers & footers. &P prints the page number.  &N prints the total number of pages in the document. &D prints the current date.  &T prints the current time. &F prints the name of the document and &A prints the name of the workbook tab.

At the end we set the worksheet to nothing and free the object and enable screen updating.

 

Here are 2 pictures.  The 1st one shows you the header and the 2nd one the footer after the macro has been run.

img1

The header has the label Company Name.  The name is not entered in yet since we haven’t linked it to any cell or fed in any text for the Company Name.  If you enter anything in the code or in the excel sheet and reference it, then the name will be picked up and populated here.

 

Page # of 0 shows that currently we have 0 pages in the file, since we have run this code on a blank file.  If you run this code on a file containing data, it will show you the page number.

 

Printed <Date><Time> gives you the date and time the macro was run along with the text “Printed”.

 

img2

In the Footer, the Path label will show you the path of the current file.

Our filename is Book1.xlsx which is currently an unsaved file. Hence there is no path showing up for the Path label.

The Sheet number is populated to the right of the footer.

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Users are saying about us...

  1. dear excel gurus,

    i’m not familiar with vba project but this piece of code was left by my former colleague. it is supposed to put page numbers on multiple files during printing.

    my question is why is it printing page numbers on other user but not mine. and that user has reported that it skips pages.

    hoping for your feedback

    Sub Button1_Click()
    On Error GoTo ErrHandler:
    Dim range1 As Long
    Dim range2 As Long
    Dim xlApp As Object ‘Excel.Application
    Dim xlWrk As Object ‘Excel.Workbook
    Dim xlSheet As Object ‘Excel.Worksheet
    Dim xlID As Integer
    Dim path As String
    Dim fileFormat As String

    path = ActiveSheet.Range(“D6″).Value
    fileFormat = ActiveSheet.Range(“D12″).Value
    range1 = ActiveSheet.Range(“E9″).Value
    range2 = ActiveSheet.Range(“E10″).Value

    If range1 <= 0 Or range2 0 Or range2 > 0 Then
    For i = range1 To range2
    Dim footer As String
    Dim fullpath As String
    fullpath = path & i & fileFormat
    Set xlApp = VBA.CreateObject(“Excel.Application”)
    Set xlWrk = xlApp.Workbooks.Open(fullpath)
    Set xlSheet = xlWrk.Sheets(1)
    Dim cnt1 As Long
    cnt1 = 1 + cnt2
    xlSheet.PageSetup.FirstPageNumber = cnt1
    xlSheet.PageSetup.CenterFooter = “Page &P”
    xlSheet.PrintOut
    HorizBreaks = xlSheet.HPageBreaks.Count
    HPages = HorizBreaks + 1
    VertBreaks = xlSheet.VPageBreaks.Count
    VPages = VertBreaks + 1
    numpages = HPages * VPages
    cnt2 = cnt2 + numpages
    xlWrk.Close False
    Next i
    MsgBox (“Done printing.”)
    End If
    Exit Sub
    ErrHandler:
    ”error handling code
    Resume Next
    End Sub

    Sub Button4_Click()
    Dim strButtonCaption As String
    Dim strDialogTitle As String
    Dim strAttachment As String
    Dim varItem As Variant
    strButtonCaption = “BrowseBTN”
    strDialogTitle = “Upload”
    With Application.FileDialog(msoFileDialogFilePicker)
    With .Filters
    .Clear
    .Add “All Files”, “*.*” ‘Allow ALL File types
    End With
    ‘The Show Method returns True if 1 or more files are selected
    .AllowMultiSelect = False
    .ButtonName = strButtonCaption
    .InitialFileName = vbNullString
    .InitialView = msoFileDialogViewDetails ‘Detailed View
    .Title = strDialogTitle
    If .Show Then
    For Each varItem In .SelectedItems
    AllowMultiSelect = False
    strAttachment = varItem
    ActiveSheet.Range(“D6″) = ExtractPathName(strAttachment)
    Next varItem
    End If
    End With
    End Sub

    Function ExtractPathName(filespec) As String
    ‘ Returns the path from a filespec
    Dim x As Variant
    x = Split(filespec, Application.PathSeparator)
    ReDim Preserve x(0 To UBound(x) – 1)
    ExtractPathName = Join(x, Application.PathSeparator) & _
    Application.PathSeparator
    End Function

  2. Pingback: Homepage

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube