3 ways to put a cell value in a page header

Ease of Use


Version tested with


Submitted by:

Ken Puls


Shows three different way to put a cell value in a header. 


This demonstrates three different macros to put a cell value in the header of a page. Since there is no way to add a formula in Page Setup, a macro must be used to accomplish this task. Each of these macros can be run before printing, and are not really intended for constant updating. If you need to sync the range name before you print every time, you will need to either call one of these macros from a BeforePrint event, or find/write a BeforePrint event to do ths for you. (Ask in the forum for help if you like.) You can also switch this to work for the page Footer by changing "LeftHeader" to "LeftFooter" in the code. 


instructions for use


Option Explicit Sub AddHeader_CurrentSheetOnly() 'Add A1 from active sheet to active sheet's header With ActiveSheet.PageSetup .LeftHeader = Range("A1").Value End With End Sub Sub AddHeaderToAll_FromEachSheet() 'Add A1 from each sheet to that sheet's header Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.PageSetup.LeftHeader = ws.Range("A1").Value Next ws End Sub Sub AddHeaderToAll_FromCurrentSheet() 'Add A1 from active sheet to each sheets's header Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.PageSetup.LeftHeader = ActiveSheet.Range("A1").Value Next ws End Sub

How to use:

  1. Copy the desired procedure from the above code (one set from Sub to End Sub).
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.

Test the code:

  1. Enter some data in cell A1 (on each sheet if necessary).
  2. Press Alt + F8 to display the Macro dialog box.
  3. Choose the name of the macro you copied and click Run.
  4. Go to File|Page Setup|Header/Footer and verify that your header has been set correctly.

Sample File:

RngA1toHeader.zip 8.83KB 

Approved by mdmackillop

This entry has been viewed 293 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express