Excel - Continuous pagination across multiple workbooks

[Pilgrimage]

I would like to be able to define my pagination across multiple workbooks.

Excel is able to paginate continuously a series of grouped worksheets if "First page number" is set to "auto" in the page setup. The issue is that I would like set the first page number in the following workbook to the last page of previous workbook +1 and then rely on Excel to automatically renumber the remaining worksheets, and so on until all related workbooks form a single printed document continuously numbered.

The reason for this is due to the fact that Excel 2003 has a limit to the number of formatting styles in a single workbook (4,000). Because the number of worksheets being put together from several different worksheets then originates a workbook exceeding the 4,000 styles limit, we would therefore need to produce smaller workbooks but then print all sequentially and properly paginated.

Is there any way one may use VBA to "influence" the first number used by Excel to then automatically paginate across the other worksheets?

I have googled and searched forums for a solution to this but could not even find an identical question. In fact, the 4,000 styles limit is an issue one does bump into frequently but, in our case, it happens often.

Thank you for any input or ideas to get around this issue.

 

[SkipVought]

Hi,

SELECT all the sheets you want to print with continuous pagination and then PRINT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Pilgrimage]

Sorry but that works for the worksheets within a single workbook only. I want to be able to paginate continuously across multiple workbooks

 

[combo]

Set the first page number only for the first sheet, leave 'auto' for the rest of selected sheets.

combo

 

[combo]

Sorry, my reply refers to single workbook only.
Is there a reason that you need 4000+ styles? Can you delete a part of them and join workbooks?

combo

 

[Pilgrimage]

Thanks combo, didn't think in that direction! Indeed, it works! Really simple...

 

[Pilgrimage]

Well, if it was my choice, the source workbooks would be properly formatted and the number of styles udes would be far less. But the final document produced my code puts together between 30 and 50 workbooks, each containing from a mere 1 up to more than 30 worksheets! Currently, the final document contains 93 worksheets but it still needs to read some more...

When I bumped into the 4,000 limit, I started reducing the styles and even removing some unnecessary formating (I used XSL Style Reduction to analyse my individual workbooks). However, there is always the risk that I bump into "too many styles" and I must therefore foresee alternatives, namely creating more than one workbook for the final document (and for this, the need to repaginate the 2nd workbook, 3rd, etc.

 

[SkipVought]

Don't believe that it works for multiple WORKBOOKS.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Pilgrimage]

Well, SkipVought, following combo's tip, it is then easy to create a macro to read a set of user-selected workbooks or contained in a folder, calculate the number of pages in the first, add last page number+1 to first worksheet of 2nd workbook, etc., until all are then paginated sequentially. One needs only to remember to always group a workbook's worksheets before printing.

 

[combo]

it is then easy to ... calculate the number of pages in the first...

It's not easy if you have more than one page printed per sheet.

You can use old macro command to calculate number printed sheets:
[tt]MsgBox Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")[/tt]
but it works only for the active sheet, whatever is selected.

Alternatively, you can analyse PrintArea, HPageBreaks and VPageBreaks for each sheet, and calculate the number of printed sheets.

combo

 

[Pilgrimage]

Indeed combo. Right now I cannot give you the eact thread reference but I found in this foum a good routine for page counting per active sheet. Given that I also have to treat the data from each worksheet, I am already looping through all and therefore activating each. I will work on this tomorrow and will probably post my code approach as it might be useful to others...

 

[SkipVought]

I think I would be apt to COPY each SHEET for all the workbooks into ONE TEMP WORKBOOK and print from there. No counting required.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Pilgrimage]

SkipVought, please have a look at the details of my question. Putting everything in a single is the reason for this discussion (4,000 styles limit)... ;-)

 

[SkipVought]

Then THAT is not "Continuous pagination across multiple workbooks!"

The answer to your question is, "SELECT all the sheets you want to print with continuous pagination and then PRINT," which was the FIRST response in your thread.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Pilgrimage]

The purpose is exactle what is indicated in subject and Combo's tip provided the necessary to achieve it. Your input however does not fit my question.

 

[SkipVought]

Wonderful! Glad you got a solution.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}