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?

 

[renerich]

Hi again....
It seems to be working now. I fixed the macro issue and closed and reopened Excel.
Thanks for your help!!!
Have a great weekend.

 

[ttobgye]

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.

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.
Please help me....I am using Excel 2007

 

[ttobgye]

Dear Peter,
Thank you for advice. The violation of forum's rules were not intended.
Can you pl. help me on the same subject. Earlier was not useful to me. I would wish if you can help me through specific steps.

 

[Peter_SSs]

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!

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.
Please help me....I am using Excel 2007

Your question is identical to renerich's so my suggestions remain as they were.

 

[ttobgye]

Dear peter,
But I am using Excel 2007. And the solutions you provided were good only for Excel 2003, if I am not wrong. Pl. Help.

Regards

 

[Peter_SSs]

.. the solutions you provided were good only for Excel 2003, if I am not wrong.

Maybe half wrong. The code I provided should work fine in Excel 2007. It is only the steps (one of them) to implement it that were not for Excel 2007.

You cannot do step 1 as I wrote it since there is no File menu with an Excel icon to the left of it in Excel 2007. Instead, use this new step 1

1. Right click any sheet name tab and choose 'View Code'. In the left hand "Project' pane double click the ThisWorkbook module of your project.

Then continue with the other steps.

 

[ttobgye]

Dear Peter,
Thanks for your prompt response. I followed exactly your instruction, but still does not work. the following message appears:

The following features cannot be saved in macro-free workbooks:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
· VB project<o></o>
To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.<o></o>
To continue saving as macro-free workbook, click Yes.
When I click Yes. Again the following message appears:
Privacy warning: This document contains macros, ActiveX controls, XML expansion pack information, or Web components. These may include personal information that cannot be removed by the Document Inspector.
<o></o>I also tried to enable Macro Setting by checking on enable macro and also checking Trust all VBA. Even with this step, same message appears:

Privacy warning: This document contains macros, ActiveX controls, XML expansion pack information, or Web components. These may include personal information that cannot be removed by the Document Inspector.


Help please.

 

[Peter_SSs]

To continue saving as macro-free workbook, click Yes.
When I click Yes...

This workbook does now contain a macro so saving as a macro-free workbook does not make sense. Click No and save as a macro-enabled workbook.

 

[ttobgye]

Dear Peter,
i tried all your instructions. but does not seem to work. can u please explain some details. Or, pl. see if i have applied steps correctly.
Steps:
1. i opened the excel workbook with multiple sheets.
2. i opened tab "Developer".
3. Then, clicked on "Macro Security"
4. I have checked "Enable all Macros (not recommended........"
5. Right clicked on one of the tab sheet....and clicked view code.
6. Double clicked on "this workbook" on Project window.
7. Then pasted following:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet

Cancel = True
Application.EnableEvents = False
For Each sh In ActiveWindow.SelectedSheets
sh.PrintOut
Next sh
Application.EnableEvents = True
End Sub
8. closed vba project.
9. saved excel workbook and closed.
10. reopened the excel workbook and tried to print by checking "entire workbook"
11. print comes like 1 of 345.

But this is not what i want. I want the page number to be somthing like for sheet 1: 1 of 20 (because it has 20 pages). Then continue for Sheet 2 as: 1 of 5 (because it has 5 pages). And continue with rest of the sheets similarly.

Is there way out for mr requirement like this.

pl. help me if u can.

Thanks

 

[Peter_SSs]

Dear Peter,
i tried all your instructions. but does not seem to work. can u please explain some details. Or, pl. see if i have applied steps correctly.

10. ... tried to print by checking "entire workbook"

I think the problem is that you have used your step 10 instead of my step 4.

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

If you look at the code itself, you will see that it is written to work on the 'SelectedSheets' and I'm presuming you only had one sheet selected when you printed.

Try right clicking any of the sheet name tabs and choose 'Select All Sheets' then try printing. (Afterwards, don't forget to right click a name tab and choose 'Ungroup Sheets', or just click on a different tab. It can be disastrous to leave sheets grouped.)