Export (0) Print
Expand All

Working with Page Headers in Excel 2010

Office 2010

Office Quick Note banner

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

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

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

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note Note

    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.

  3. In the Projects pane, click Sheet1.

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

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.

  3. Now, press F8 to step through the code line-by-line and watch the code behavior.

Show:
© 2014 Microsoft