Need a macro to sum or subtotal ranges of varying lengths in a column

I've reached a point in this macro where I'm stuck with NO clue how to achieve this, or even whether it can be done. In the Before sheet below, I need to bold the cells in H & I if the word "Total" is in G. I could probably manage that part, but here's the weird part: I need to add a formula to H & I of each row with "Total" in G to sum the numbers above it, from the previous total down to the current total. But there's no telling how long each range might be on a given report; from 1 row up to 8 rows.

THEN, in the second row below the end of columns H & I, I need to put a formula totalling all of the preceding Totals

I wonder if there might be a way to tell it to put a formula in H by each cell that has "Total" in G, with the formula summing or subtotalling everything from the cell in H that has data in F to the cell just above the "Total". Any ideas?

I'm trying to attach a file; I hope it works!

Thanks!

Jenny

With the code below to fill the date.

Please Login or Register  to view this content.

after that I made an table and after that a pivot table of the data.

also a graph is added to the file.

see the attached file.

Maybe:

Please Login or Register  to view this content.

Another question:

This report has a lot of data after this Summary section that's at the top. This data is formatted completely different from the Summary section. So, in my larger macro that I want to add this to, I've set SL as the row that has "Grand Total" in column F, denoting the end of the Summary section. (I totally forgot to include the Grand Total line before. VERY sorry!) So, I changed the coding to this:

Please Login or Register  to view this content.

But that gives me the result in the After tab on the newly attached workbook.

I also tried replacing all of the

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

to try to confine the activity of the macro to the Summary section. But that caused the Grand Total line to move up directly below the last Total line, with H & I containing some HUGE number more than twice what you'd get if you summed ALL the lines above it & then added another line right below that which sums all the data above (giving twice the total needed, so obviously THAT'S wrong!

I'm going to be SO happy with this when it's done, but it sure is a process getting there, LOL!
Thanks!

Jenny

Hi John,
I'm back! Had to take care of some month-end work.

I wanted to confine the macro to just the top section of the worksheet - the Summary section. So, I made a couple of additions to your already awesome code! I want it to stop when it gets to the row with "Grand Total" in column F. So, here's what I have now:

Please Login or Register  to view this content.

My only remaining problem is that, near the end of the code, it specifies J13:J31, which is how long the section is on the workbook I posted. But the Summary section is always longer than that, I just never know HOW long it's going to be. I tried replacing "=SUM(J13:J31)" with "=SUM("J13:J" & SL.Row - 3)", but that gives me a Compile error: Expected: end of statement on the J13. So, apparently, I can't use the SL.Row - 3 there or else I've got the syntax wrong. Ideas?

Thank you for your help!

Jenny

Edited: I got the Compile error to go away by using double quotes around J13:J. So, now it recognizes the SL.Row - 3, but it seems to want to back up too many lines. So, on the line that cuts the empty rows, it cuts clear up & includes the last Total row. And the Grand Totals return #NAME, plus the last cells in J and K don't get cleared. I'm not sure what I did wrong, but I'll attach the latest version of the workbook. And, now the end of the code looks like this:

Please Login or Register  to view this content.

Also, I'm curious - in this line of code:

Please Login or Register  to view this content.

what does the (3)(7) mean? I'm thinking that might be part of my problem, but I can't figure it out.

Jenny