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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When you have a worksheet that includes a long list of names, you may want the first and last names on each page to ...
Discover MoreYou can easily create headers and footers for multiple worksheets by working with a selection set of the worksheets you ...
Discover MoreNeed to copy headers and footers from one worksheet to another? How about from one workbook to another? Here are some ...
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 © 2019 Sharon Parq Associates, Inc.
Comments