• 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.

  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.

 

 

Discuss

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