Page number sheets when printing Entire Workbook

[rdance]

I am trying to find a way to print an "entire workbook", but have each of the sheets paged as a group separate from the rest. One workbook typically has up to 20 sheets, with each sheet up to roughly 8 pages. I need each sheet set to show page 1 of 8 or page 5 of 8, not page 22 of 53. Counting and typing in the total number of pages in each sheet's footer is too cumbersome. Also, printing each sheet individually has also been cumbersome when printing to PDF. Are there any other ways to have the "&[Pages]" function only reflect the number of pages within the sheet instead of in the entire workbook?

 

[Datsmart]

Take a look at this post:
http://www.mrexcel.com/board2/viewtopic.php?p=1231646

 

[rdance]

I recently went back to the post you suggested, since I need the page numbers to be embedded in the footers (not in a cell). I have the same questions as jabba9. I need to print the entire workbook at one time, but only the page numbers associated with each sheet to print. Any ideas?

Thanks!

 

[renerich]

Have you ever found an answer to this other than to just print each sheet individually? I have the same issue and it is making me nuts.
The macro is not the answer I am looking for.

 

[Peter_SSs]

renerich

Welcome to the MrExcel board!

If you have 3 tabs and each tab is 2 pages, do you need your footers to be

1 of 2
2 of 2
1 of 2
2 of 2
1 of 2
2 of 2

or just
1
2
1
2
1
2

 

[renerich]

I need page 1 of 2, page 2 of 2, page 1 of 3, page 2 of 3, page 3 of 3, etc. Some sheets are multiple pages, others are not. I want each sheet to be unique in it's numbering.
Thanks for the quick response!

 

[Peter_SSs]

If you have something like this in the footer section of each sheet:
Page &[Page] of &[Pages]

Then try this in the ThisWorkbook module. Post back if you need help with how to implement it.


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforePrint(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> sh <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    Cancel = <SPAN style="color:#00007F">True</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sh <SPAN style="color:#00007F">In</SPAN> ActiveWindow.SelectedSheets<br>        sh.PrintOut<br>    <SPAN style="color:#00007F">Next</SPAN> sh<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[renerich]

Thanks Peter.
I do have exactly what you show for the footer. However I have no clue what you are talking about in the rest of the message?????
I am not technical and really do not want to make this a lot of work for you or me. So if it is something I can easily do and you tell me how, cool. Otherwise I will continue to print one sheet at a time which works but is more time consuming than I would like.
Up to you how we proceed.

 

[Peter_SSs]

Thanks Peter.
I do have exactly what you show for the footer. However I have no clue what you are talking about in the rest of the message?????
I am not technical and really do not want to make this a lot of work for you or me. So if it is something I can easily do and you tell me how, cool. Otherwise I will continue to print one sheet at a time which works but is more time consuming than I would like.
Up to you how we proceed.

It is not hard, just follow these steps (assuming not Excel 2007 - post back again if you are using that version) ...

1. Right click the Excel icon immediately to the left of the 'File' menu and choose "View Code".

2. Copy my code from the above post and paste it into the main right hand pane that opens at step 1.

3. Close the Visual Basic Window.

4. Try selecting all your sheets (or any group of them) and do a Print Preview to have a look at the resulting footers.

Edit: One further thing with this method, you will have to 'enable macros' to allow this code to run. If you save, close and reopen the workbook, you may get some message about macro security. If you need help with how to deal with this, post back with the message you get.

 

[renerich]

Hi Peter.
Thanks again for your assistance. I applied the code and now I have lost the option to preview multiple sheets and when I go to print I dont have the entire workbook option since the window does not pop....it just prints. It is possible I applied something incorrectly.
All I did was cut and paste your code from above in the window that opened. Saved it and then previewed and printed.
Thoughts?