Headers - Print No of Pages in worksheet (Not workbook)

Using Excel 2013

Inserting a header, within the DESIGN tab on the ribbon, under the Header & Footer Elements, you are able to enter Page Number or the Number of Pages. This makes it easy to say PAGE 1 of 5 (or however many pages you have). And the Number of Pages updates as you change the workbook.

This works perfectly....if I am only printing from that single worksheet.

But as soon as I Print Entire Workbook, or select multiple worksheets at once and print active sheets, then the number of pages naturally changes to be the number of pages in the workbook. This is useful on occasion.

But I would like to change this so that it remains Number of Pages within the worksheet. This way, when I print entire workbook, each individual worksheet will have its own section of Page # of #.

(And the solution cannot be "Print out each worksheet individually" as I have many worksheets and that would be painstaking.)

Thanks for the reply. Bummer!

Are you able to provide a suggestion on the code?

A simple test by selecting all sheets and running code:

Please Login or Register  to view this content.

Did not work successfully as the Number of pages corresponded to the number in the selected range...not per each sheet.