cfo.com

Print this article | Return to Article | Return to CFO.com

Spreadsheets: Use a Macro to Solve Header-aches

Linking cell headers and footers across multiple worksheets is automated using some simple VBA coding.
Bill Jelen, CFO.com | US
May 18, 2011

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:

  1. Type Alt+F11 to open the Visual Basic Editor;
  2. Type Ctrl+R to open the Project Explorer. You will see a list of all open workbooks and any installed add-ins;
  3. Click the Plus sign next to your workbook to see a list of all worksheets and something called ThisWorkbook;
  4. Double click on ThisWorkbook to open the code pane for the workbook. You will see two dropdowns above the code pane;
  5. From the left dropdown, choose Workbook;
  6. From the right dropdown, choose BeforePrint;
  7. Copy the following code into the code pane:

            Private Sub Workbook_BeforePrint(Cancel As Boolean)

            Dim ws As Worksheet

            For Each ws In ActiveWorkbook.Worksheets

            ws.PageSetup.RightHeader = "Printed by " & Application.UserName

            ws.PageSetup.LeftFooter = Worksheets("IncStmt").Range("Z1").Value

         Next ws

         End Sub

The macro posted above will insert a system variable for your name into the right header. If your workbook is stored on a shared network drive, this will be useful for telling which co-worker printed the document. It will also take the current value from cell Z1 of the IncStmt worksheet and insert it as the left footer. You can customize the worksheet name and range name to get the appropriate cell in your footer.

What if you wanted cell Z1 from each worksheet to be used as the center footer for that worksheet? Change the macro to:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

  ws.PageSetup.CenterFooter = ws.Range("Z1").Value

Next ws

End Sub

In this macro, the footer for Sheet1 will use Z1 on Sheet1. The footer on Sheet2 will use Sheet2!Z1, and so on.

Avoid the XLSX File Type
In Excel 2007 and newer versions, you can save files as .XLS, XLSB, or .XLSM with this method. The bizarre .XLSX file type strips out any macro code, so you will want to use Save As to change the file type if your file is currently saved with the .XLSX extension.


Also, if you have never used macros before, check your macro security settings. Type Alt+T followed by M and S. In Excel 2003, use the setting of Medium. In Excel 2007/2010, use Disable All Macros With Notification. In both of these scenarios, Excel will ask you to enable the macros when you open the workbook.

CFO contributing Editor Bill Jelen is the author of 32 books about Microsoft Excel, including VBA & Macros for Excel 2010. You can win a copy of one of his books if you post a question at the Community Center at the right and it is selected as the topic of a future column.

 

 




CFO Publishing Corporation 2009. All rights reserved.