Printing Page Numbers for Multiple Worksheets

I am trying to print an entire workbook and would like the page number
to be specific to each worksheet set.

For example, my workbook contains 7 sheets, each of which has a unique
header that contains the command for Page X of X Pages. Each sheet
contains multiple pages.

If I print the entire workbook, I can get the printout for each sheet
to begin with a 1 by manually setting the First Page setting to "1" in
Page Setup. No problem there.

However, my question is...Can I control the command for "X Pages"? For
example, when I print the entire workbook, I'd like the first page for
Sheet 1 to read "Page 1 of 4 Pages" (when there are 4 pages in this
sheet), Sheet 2 to read "Page 1 of 3 Pages", and Sheet 3 to read "Page
1 of 7 Pages". The best I get right now is that each Sheet reads Page
X of 14 Pages" (the total of all pages in the print job.

Any ideas? This would be a huge time saver for me.


---
Message posted from http://www.ExcelForum.com/

Hi Greenguitar,

Any ideas? This would be a huge time saver for me.

I guess a macro would do:

Sub PrintSheets()
Dim oSH as Worksheet
For Each oSH in Activeworkbook.Worksheets
oSH.Printout
Next
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com