Dynamic page breaks for Pivot Tables

[BillXL]

I’m trying to figure out a way of printing a Pivot Table and inserting page breaks in an ‘intelligent’ way.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

My pivot table is a list of employees, grouped by department.<o></o>
Some of the department fields have only one or two employees, others have more than 50. <o></o>

So I have a report of 20 departments and each department has a variable number of employees (rows).<o></o>

I don’t want to insert a page break after each field, as this would result in many pages being printed – some of which would only have 2 or 3 rows.<o></o>
Also, I’d rather avoid having a static row length for each page – because I would like the break to be after a subtotal where possible.<o></o>
<o></o>

What I have in mind is this. I’d like to set the maximum number of rows to 40 per page. Say I have the following departments & the total number of rows (employees & subtotal) is:<o></o>
Department A, 25 rows; Dept B, 4 rows; Dept C, 5 rows; Dept D, 10 rows.<o></o>
In this case, I’d like the page break to be place after Department C. (Total rows of the first 3 departments is 34. The next department has 10 rows, so the total is over 40.)<o></o>

This has to be dynamic. If the pivot table is refreshed and the Department A in the example has 20 rows instead of 25, then I’d like to have the page break after Department D. <o></o>
<o></o>
I should be able to figure out a VBA way to do this – but I am struggling to find a way to count the number of data items (i.e. employees) in each field.<o></o>
<o></o>
Any help would be greatly appreciated.<o></o>
<o></o>
<o></o>
Many thanks<o></o>
<o></o>
Bill<o></o>

 

[BillXL]

Any ideas on this?

I thought I was closer to a solution - I have set the pivot table fields to display a blank line after each department. So I am now able to identify where each field begins and ends, but I am still stuck trying to group some fields together for printing.

Any thoughts?

Bill