Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Selective Headers and Footers.
by Allen Wyatt
(last updated January 16, 2014)
One of the long-time complaints about Excel is that it doesn't have a very robust method of creating and managing headers and footers. Consider the following scenario: You want to print your worksheet, but only have page numbers beginning on the second page.
There is no intrinsic method in Excel to handle this situation. There are some workarounds; for instance, you could put your first page on one worksheet (without headers or footers) and the subsequent pages on a different worksheet (which includes headers and footers). You could then print the two worksheets in one pass, and effectively achieve your goal.
If you have the Report Manager installed, you could use it to put together different reports based on the information in your worksheet. Using the Report Manager has been covered in other issues of ExcelTips. The Report Manager add-in was last distributed with Excel 2002, but you can still use it in Excel 2003. This Knowledge Base article explains how you can use it:
Neither of these approaches work for all situations, however. For instance, you may not be able to split your printout into multiple worksheets, or you may not have much experience with the Report Manager (or you don't want to download and install it). If you prefer, you can create a macro which will print your worksheet as you desire.
The following macro, GoodPrint, will print the first page of a worksheet without headers or footers, and then all subsequent pages as normal.
Sub GoodPrint() Dim hlft As String Dim hctr As String Dim hrgt As String Dim flft As String Dim fctr As String Dim frgt As String 'save current header hlft = ActiveSheet.PageSetup.LeftHeader hctr = ActiveSheet.PageSetup.CenterHeader hrgt = ActiveSheet.PageSetup.RightHeader 'save current footer flft = ActiveSheet.PageSetup.LeftFooter fctr = ActiveSheet.PageSetup.CenterFooter frgt = ActiveSheet.PageSetup.RightFooter 'remove header and footer With ActiveSheet.PageSetup .CenterHeader = "" .RightHeader = "" .LeftHeader = "" .CenterFooter = "" .RightFooter = "" .LeftFooter = "" End With 'print page one ActiveSheet.PrintOut 1, 1 'restore header and footer With ActiveSheet.PageSetup .LeftHeader = hlft .CenterHeader = hctr .RightHeader = hrgt .LeftFooter = flft .CenterFooter = fctr .RightFooter = frgt End With 'print the rest of the pages ActiveSheet.PrintOut 2 End Sub
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2189) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Selective Headers and Footers.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When preparing a worksheet for printing, you may want to include in the header or footer the last date the workbook was ...Discover More
Want to add some color to the printing of your page headers and footers? Your options are limited, as disclosed in this tip.Discover More
Changing the look of default headers and footers for your Excel workbooks.Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."