Page Breaks and Subtotals

[Yellowbird]

Hopefully this makes sense and someone can assist with this relative
newbie's pagebreak/subtotals question.

I've got a worksheet with data as follows:

Type A Type B Total By Location
Location A 0 1 1
Location B 1 4 5
Total By Type 1 5

The report is variable length (both rows and columns) and I want to be
able to create a macro to search for each pagebreak (HPageBreak and
VPageBreak) and insert a subtotal just before (so the subtotal is the
last row and column of each page). Then, on the last page of the
report, have a final Subtotal and Grand Total row and column.

I've searched for ideas and it seems that Data | Subtotals might work,
but I am fairly new to Excel and can't seem to get it to work without
getting a subtotal in every other row, which I don't want.

Can anyone offer a suggestion on a way to do this?

TIA,
Yellowbird

 

[Tom Ogilvy]

There is no built in function to do this.

You would have to search through the HPageBreak collection from top to
bottom (since as you insert coming down, the breaks will change), insert a
row above the pagebreak and put in the formula to sum. then do the
VPageBreak from left to right.

 

[Debra Dalgleish]

One way to use subtotals:

Insert a new column in the table, with the heading "Page"
In the cell below, enter a formula to calculate the page number, e.g.:
=INT(COUNTA($A$1:A2)/56)+1
where 56 is the number of rows per page
Copy the formula down to the last row of data
Select a cell in the table, and choose Data>Subtotals
Choose to group at each change in "Page"
Select the fields you want to total
Add a check mark to 'Page break between groups'
Click OK

You can hide the Page column, or exclude it from the print area.