• Technology
  • CFO.com | US

Spreadsheets: Use a Macro to Solve Header-aches

Linking cell headers and footers across multiple worksheets is automated using some simple VBA coding.


Editor’s Note:

 
To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column,
 

click here

 
to visit CFO.com’s Spreadsheet Tips Page.

Wayne R. wins a copy of Learn Excel 97-2007 from CFO and MrExcel.com for his question, “How do I link a cell to the header so I only need to change my cell reference once when I change multiple worksheet headers?”

This is an excellent idea, but Microsoft does not offer an easy solution. More than a decade ago, Microsoft realized the need for this type of header, but rather than add the feature to Excel, they posted a one-line macro to the Microsoft Knowledge Base. In this article, we’ll take a look at the valid codes that can be placed in a header or footer, and then look at a better macro for solving Wayne’s question.

Built-In Header Codes

In Excel 2003 and earlier, you could maintain the header and footer using File, Page Setup. In Excel 2007, you can use the Dialog Launcher in the bottom right corner of the Page Setup group on the Page Layout ribbon tab, or you could choose View, Page Layout, then click in one of the three header or footer zones. You can type any text in a header or footer. You can also use one of these codes to insert some dynamic text:

     &[Page] – inserts the current page number

     &[Pages] – inserts the total number of pages

     &[Date] – Inserts the “short date” format, such as 5/18/2011

     &[Time] – Inserts the time in a format such as 9:00 AM

     &[Path] – Inserts the file path where the current workbook is saved

     &[File] – Inserts the name of the workbook

     &[Tab] – Inserts the worksheet name

     && – Inserts a single asterisk

Following the logic posted above, you might think that &[Sheet1!Z1] would be a convenient way to solve Wayne’s question. Unfortunately, Excel does not support such a code.

Using a Macro

A small macro will allow you to change the headers and footers on the fly. The macro provided by Microsoft will take the current value of any cell and insert it as static text in one of the six header or footer zones. This means that you will need to run this macro immediately before you print your workbook. Luckily, there is a built-in event handler macro that will run between the time when you click Print and before the printing actually starts. Follow these steps to create a BeforePrint macro:

Discuss

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