Use Cell Contents in a Footer in Excel

The header and footer options in Excel are very useful for printing information related to the workbook such as the page number, the saved workbooks filename and path or the date. However, what if you want to use the contents of a cell in a footer in Excel.

The header and footer options do not allow the use of cell contents in a footer, so we will have to use a VBA solution.

The Workbook BeforePrint Event

We will achieve this by using the BeforePrint event of the Workbook object. The BeforePrint event is run before you print, or preview, anything in an Excel workbook.

By entering code into the event procedure in the workbook module, Excel will print the contents of a cell into the footer just prior to printing or previewing.

1. Click the Developer tab and then Visual Basic or press Alt + F11
2. Double click on the ThisWorkbook object in the Project Explorer to open the required code module

3. Click on the Object list arrow and select Workbook

4. Click on the Procedure list arrow and select BeforePrint

5. The container code for the BeforePrint procedure appears in the code module. Enter the following code between the container lines as shown below

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Object
For Each sh In Me.Sheets
sh.PageSetup.RightFooter = Worksheets("Sheet1").Range("A1")
Next sh
End Sub

6. Close the Visual Basic Editor window

This code declares a variable named sh as an object so that it includes both worksheets and chart sheets. It then runs a For Each loop to print the contents of cell A1 of Sheet1 on the right of the footer of every sheet in the workbook.

The BeforePrint event can be used to apply many other types of automated print procedures such as changing page orientation, hiding columns or the number of copies printed.

This example demonstrates it being used to effectively use the contents of a cell in a footer in Excel.