Retain worksheet page numbering when printing multiple worksheets at once

Hi V,

I don't have the full solution for your (and my) problem, but resetting of page numbering can be set on Page Setup/Page tab. Here there is a field for entering the number of the first page . It is set to Automatic by default, but if you enter 1 here, then all worksheets will begin with 1 if you select multiple sheets. The other part of the problem (that of resetting the total pages for each workheet) remains unfortunately still outstanding. So it must be "hardcoded" for every workheet.

Cheers,

RG

Hi Leon,

So to summarise, there is no setting within Excel that retains the worksheet-level page numbering when you select multiple worksheets to print at the same time. (Note that if anyone else reading this knows otherwise, I would be very grateful to hear from you!)

My workaround options therefore are:

1. retain the "Pg &[Page] of &[Pages]", and print each worksheet individually
2. change the paging to "Pg &[Page]-x of Tot_Worksheet_Pages", where Total_Worksheet_Pages is a hard-entered constant representing the number of pages in that worksheet, and the x in &[Page]-x represents the total number of pages in the set to be printed less the constant needed to achieve the desired page reference for that particular worksheet - noting that if you change the set of worksheets being printed or increase/decrease the page total, you will need to update all of the "&[Pages]" and some/all of the x's within each worksheet.

Appreciate your time Leon and I'm pleased to learn of the clever -x function in page numbers - that will be handy at other times, but I think printing worksheets individually will remain my workaround in the meantime.

Cheers

V

Thank you Jan, I am very grateful for this code, and this solution. It is a big help to me.

I note that when I print it to pdf, it asks me to save multiple times (ie each worksheet pdf is saved separately). Is there a way to get it to save all selected worksheets to one pdf (but with worksheet-level page numbering)?

Thank you again Jan.

Hi Leon,

I can see how this would work but am I right in thinking that as soon as the number of pages in one of my worksheets changes, I will have to change the #pages on that worksheet to the new #pages constant and I will have to change the &[Page]-x number for that and all worksheets following as well?

And if I do a different subset of worksheets to print, I would need to manually go into each worksheet's footer and change the -x value so that it prints correctly..?

Thanks

V

Hello, I am Leonielhou, an Independent Advisor and a user like you, I am happy to help clarify any questions you may have.

Click on the first worksheet tab, and then click on the Page Layout tab on the ribbon. Click on the arrow in the Page Setup group to open the Page Setup dialog box. In the Page Setup dialog box, click on the Header/Footer tab. Click on the Custom Footer button, and in the Footer dialog box, type "Page &[Page] of 3" (without the quotes) in the Left section. This will ensure that the page numbers for the first worksheet show as "Page 1 of 3", "Page 2 of 3", and "Page 3 of 3". Click OK to close the Footer dialog box, and then click OK again to close the Page Setup dialog box. Repeat the above steps for the other worksheets, but modify the custom footer to match the number of pages in each worksheet.