Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Breaks and Subtotals
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Breaks and Subtotals
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. -- Regards, Tom Ogilvy "Yellowbird" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Breaks and Subtotals
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 DataSubtotals 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. Yellowbird wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I can't move my page breaks in Page Break Preview | Excel Discussion (Misc queries) | |||
When automatic page breaks are moved each cell becomes a new page | Excel Discussion (Misc queries) | |||
Vertical page breaks won't drag in Page Break Preview | Excel Discussion (Misc queries) | |||
How do I do page breaks when view menu doesnt page break preview | Excel Discussion (Misc queries) | |||
Page breaks when using subtotals | Excel Worksheet Functions |