I've attached a file with a couple of sheets that are part of a larger project. The procedure I am having difficulty with is activated by the "Team Totals" button on the "Summary Data" worksheet (which produces the "Team Totals" worksheet with some Subtotals calculated - to run same, make sure to delete the existing Team Totals worksheet first).
What I am trying to do is this: The spreadsheet user has indicated that the "Team" Subtotals aren't meaningful but in their place on the inserted Subtotal Rows, he would like to see the last quarterly totals for the Team rather than a sum of the DataSet. Manually, I can do this by:
1) collapsing the outline to the Team Totals (Outline Level 3), and then
2) selecting the cells in those subtotals rows,
3) doing a Goto (F5) Special | Visible Cells (though this last probably isn't necessary),
4) then expanding the outline to the Full detail (Outline Level 5),
5) in the active cell, typing the formula "=" followed by an Up Arrow key, and
6) finishing the formula with a CTRL+Enter to update all of the selected visible cells, with their own relative formula to retrieve the last quarter's value for the Team.
However, I can't figure out a way to dynamically (with VBA) determine the Sub-total rows only to apply this treatment to. This is the main problem I am trying to solve.
For extra credit ;~), I'm also having these issues:
a) When I get that part finished, I want to copy and paste the entire sheet as Values (but I've been getting "The information cannot be pasted because the copy area and the paste area are not the same size and shape..." etc. errors doing that (I guess because either a filter or Sub-totals have been applied when I've done that).
b) Somehow in my code, I have added an "extra" level to the outline that is not functional (clicking same - Level 2 - shows no data), and when the existing procedure finishes, it seems to have super-imposed a level with all of the blank rows that weren't populated with data and that needs to go away ...
If someone can assist with the VBA to fix these things, too, that would be great.
Thanks for any insights!
(BTW, while I am using Excel 2007 on this project, since EE won't accept an "XLSM" file as an attachment, I have saved the file as an XLS format)