• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / VBA / Use Cell Contents in a Footer in Excel

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
Use the ThisWorkbook object in the Project Explorer
  1. Click on the Object list arrow and select Workbook
Selecting the Workbook object
  1. Click on the Procedure list arrow and select BeforePrint
Use the BeforePrint procedure
  1. 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
  1. 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.

Related Posts:

  • Excel Formula to Find the Cell Address of a Value
    Excel Formula to Find the Cell Address of a Value
  • Count the Number of Words in a Cell
    Count the Number of Words in a Cell
  • Sort by Drop Down List in Excel
    Sort by Drop Down List in Excel
  • What You Need to Know About Dynamic Array Formulas in Excel
    What You Need to Know About Dynamic Array Formulas in Excel

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • Conditional Formatting Multiple Columns – 3 Examples
  • IF Function in Power Query Including Nested IFS

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·