Excel

3 ways to put a cell value in a page header

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Ken Puls

Description:

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

Discussion:

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. 

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 304 times.

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