Tips & Tricks 152 - While Printing a Sheet, Don't Print Page Number on First Two Pages and Print 1 on 3rd Page
One user had a specific requirement. His first two pages were cover page and Table of Contents. Hence, he wanted to start his page number from 3rd page. He wanted to display page X of Y. Hence, if the worksheet had 10 pages, his 3rd page should say 1 of 8......Last Page should say 8 of 8.
The above problem will have to be solved through VBA.
1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double click on ThisWorkbook
5. Copy paste the Macro code given
7. Save your file as .xlsm
8. Create a button and attach this macro to that button
' ** Vijay A Verma (eforexcel.com) **
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
ActiveSheet.PrintOut From:=1, To:=2
ActiveSheet.PageSetup.CenterFooter = "&P-2 of " & ActiveSheet.PageSetup.Pages.Count - 2
ActiveSheet.PageSetup.CenterFooter = ""
Application.EnableEvents = True