Headers in Excel

Notes on how to set up "dynamic" headers in Excel so you can update the variable data within a worksheet and through running a Macro will then update in each page header.  This allows you to avoid having to update the header data individually on each worksheet.

-------

DO NOT RENAME THIS WORKSHEET TAB - IT MUST BE "Sheet4"

 

Information to include in cell A3:  "Proposal Dated: 21 November 2011"  Note, this RightHeader information MUST remain in cell A3

 

To insert this information into your header:

1) SAVE your file

2) SAVE AS a new revision (note, you must select "Save as type" as "Excel Macro-Enabled Workbook")

*These two steps are important because this "header process" is done by MACRO

There is no way to UNDO a MACRO once it has been run             

You can overright the information by changing the text in cell A3 and then rerunning the MACRO            

But, if something goes amiss and messes up all the other page formatting you will be very upset that you skipped these two steps, I promise.

3) Click on the "Developer" tab

4) Click on the "Macros" button

5) Select:

"ThisWorkbook.PrintReport"

6) Click "Run"

Note, if your Developer tab isn't showing:

Right click on any other tab (ie Page Layout)

Select "Customize the Ribbon"

On the right side of the window, make sure the check box beside "Developer" is checked           

 

The macro code should be added to "ThisWorkbook" under the "Genaral" tab as a "Print Report" and is written as follows:

 

Sub PrintReport()

    Dim wks As Worksheet

    Dim ftr

    ftr = Sheet4.Range("A3").Value

    For Each wks In Worksheets

        With wks.PageSetup

            .RightHeader = "&""Times New Roman,Italic""" & ftr

        End With

    Next wks

End Sub