Working with Page Headers in Excel 2010
Handy Programming Tips for Microsoft Excel 2010: Learn how to programmatically work with page headers and footers in Microsoft Excel 2010.
Last modified: May 20, 2011
Applies to: Excel 2010 | Office 2010 | VBA
Published: June 2011
Provided by: Frank Rice, Microsoft Corporation
In Microsoft Excel 2010, headers and footers are lines of text that print at the top (header) and bottom (footer) of each page in the spreadsheet. In this topic, you add headers or footers at the top or bottom of a printed worksheet and then see the use of some of their properties in action. To complete this task, you must do the following:
In this task, you add programming code that works with various properties of the workbook page headers and footers.
To add code to the Visual Basic Editor
Start Excel 2010.
On the Developer tab, click Visual Basic to open the Visual Basic Editor.
If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.
In the Projects pane, click Sheet1.
Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.
Sub WorkWithPages() ' Fill random data: Range("A1", "R100").Formula = "=RANDBETWEEN(1, 100)" Dim pgs As Pages Set pgs = PageSetup.Pages PageSetup.DifferentFirstPageHeaderFooter = True ' Look in the Immediate window for this output: Debug.Print "The current sheet can be printed on " & _ pgs.Count & " page(s)." Dim pg As Page Set pg = pgs(1) pg.CenterHeader.Text = "This is the first page's header" Set pg = pgs(2) pg.CenterFooter.Text = "This is the second page's footer" Set pg = pgs(pgs.Count) pg.CenterFooter.Text = "This is the last page's center footer." pg.LeftHeader.Text = "This is the last page's header" ' Note that Excel supports only distinct headers/footers ' for the first page, so headers and footers on the second ' and other pages are combined--the last value set overwrites ' the header/footer. ' See the values in the Immediate window. ' Note that the code disregards errors that occur--attempting ' to retrieve a header/footer setting that doesn't exist raises an error: On Error Resume Next Debug.Print "First page (CenterHeader) : " & pgs(1).CenterHeader.Text Debug.Print "Second page (CenterHeader): " & pgs(2).CenterHeader.Text Debug.Print "Second page (CenterFooter): " & pgs(2).CenterFooter.Text Debug.Print "Third page (CenterFooter) : " & pgs(3).CenterFooter.Text Debug.Print "Last page (LeftHeader) : " & pgs(pgs.Count).LeftHeader.Text Debug.Print "Last page (CenterFooter) : " & pgs(pgs.Count).CenterFooter.Text ' In conclusion, use the Page class to retrieve information about headers ' and footers for specific pages. Use the PageSetup object to set the headers ' and footers, as it's clearer to set them there. End Sub
In this task, you step through the VBA code that manipulates the headers and footers in the workbook.
To step through the code
Drag the Visual Basic Editor window to the right side of your monitor.
Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.
Now, press F8 to step through the code line-by-line and watch the code behavior.