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 prefer, you can create a macro that prints 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
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12101) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Selective Headers and Footers.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Today's date is easy to add to a header, but what if you want to add a date that is adjusted in some manner? Adding ...
Discover MoreWhen printing a worksheet, you may want to have the footer different on the first page of your document than it is on ...
Discover MoreExcel won't let you place a formula directly into a footer. You can, however, create a simple macro that will produce the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-02-20 10:33:16
Robert Ch
You can have a "different first page" without using macros.
On the print, page setup, go to the tab "Header/Footer" and place a tick mark on "Different first page"
Then, when you select "Custom Header" or "Custom Footer" you will have an option to do a generic header/footer and another tab with "First Page Header/Footer"
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Comments