Excel

Create Header or Footer

Ease of Use

Easy

Version tested with

2000 

Submitted by:

Anne Troy

Description:

Changes the header and/or footer of your Excel file to ensure they print on any page, regardless of manual changes someone may have made to the file. 

Discussion:

This particular example grabs a value from a cell in the workbook. Suppose you want to report for several departments. So, you autofilter the data, type the particular department name into cell A3, and print. Change the autofilter to another department, change A3 again, and print. This code works in the workbook before print event, so it isn't good if you want to use a different header and/or footer on each worksheet. 

Code:

instructions for use 

			
Option Explicit 
 
Private Sub Workbook_BeforePrint(Cancel As Boolean) 
     
    Dim WS As Worksheet 
    For Each WS In Worksheets 
         'Change LeftHeader to any of the following as desired
         'CenterHeader, RightHeader, LeftFooter, CenterFooter, and RightFooter
         'After the = sign, you can just place text between quotes if desired
         
        WS.PageSetup.LeftHeader = ThisWorkbook.FullName & " " & _ 
        Worksheets("Sheet1").Range("A3").Text 
         
    Next WS 
End Sub

How to use:

  1. Copy the code above.
  2. Open any Excel file and hit Alt+F11 to open the Visual Basic Editor (VBE).
  3. Double-click ThisWorkbook at left beneath your file name.
  4. Paste the code into the code window at right.
  5. Change the sheet name and cell reference as desired.
  6. Save the file, and close the VBE.
 

Test the code:

  1. Print the worksheet.
 

Sample File:

headerfooter.zip 6.59KB 

Approved by mdmackillop


This entry has been viewed 208 times.

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