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.PrintOut From:=3
    ActiveSheet.PageSetup.CenterFooter = ""
    Application.EnableEvents = True
End Sub