November 16, 2014

Automatically Add Header Footer to Excel Document with VBA

How can we add a header or footer to an Excel document automatically with VBA. Let’s first find all the built-in properties of an Excel document that can be accessed using a ‘for next’ loop as described in this article ‘Workbook.BuiltinDocumentProperties Property (Excel)‘:

Sub inbuiltDocumentPropertiesList()
rw = 2
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
rw = rw + 1
End Sub
We can now add a header or footer or any other built-in property into an Excel worksheet using the following VBA code:
Sub addHeaderFooter()
ActiveSheet.PageSetup.LeftFooter = _
ActiveSheet.PageSetup.CenterFooter = _
ActiveWorkbook.BuiltinDocumentProperties(“Creation Date”)
ActiveSheet.PageSetup.CenterHeader = _
End Sub

The above code will display the name of the author on the left in the footer. The date of creation of the file will be displayed in the center of the footer. The header will also display the author’s name in the center of the header area. You can also display other built-in properties like ‘Keywords’. The built-in properties can also be displayed in the worksheet cells by modifying the VBA code appropriately.

Watch the video below:

Download a sample file by clicking on the Excel icon:

Further reading:
Workbook.BuiltinDocumentProperties Property

2 thoughts on “Automatically Add Header Footer to Excel Document with VBA

  1. Sir i want change header and footer for multiple workbooks at a time
    could you give me a VBA for this .Thanks

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.