2000 vs. 2003: Grand Totals

[Guest]

Just finished the hotfix mentioned in article 831824 regarding multilevel
subtotals being in the wrong position and it seems to work great. However,
it seems (so I'm told) that our macros created in Excel 2000 would also
include extra lines of Grand Totals that are apparently not present when
these are run in Excel 2003. The problem is that the macros are set up to
manually remove these grand total lines (which now has the effect of removing
subtotals and corrupting the report instead.) As I don't relish the idea of
reprogramming everything we have, is there some way to make Excel 2003 run
the way it used to regarding the grand totals?
Thanks

 

[Guest]

Just tried this out on Excel 2000 so I've found the offending code. Still
would like a fix that will work instead of manually going through every
program we have:

Range("A5:" & LastCell).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5,
7, 10, _
12, 13), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Range("D6").Select
Selection.End(xlDown).Select
LastCell = ActiveCell.Offset(0, 9).Address

Range("A5:" & LastCell).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5,
7, 10, _
12, 13), Replace:=False, PageBreaks:=False, SummaryBelowData:=False

So basically on the second subtotal, the SummaryBelowData:=False isn't fully
working as I get an extra grand total in column 2 immediately above the first
grand total in column 1. In Excel 2003 the extra grand total isn't there,
but has the effect of breaking our reports. So it almost seems like it was
an Excel 2000 bug. Any ideas of an efficient fix to this short of hours and
hours of programming?

 

[Myrna Larson]

The method

Application.Version

will give you the version of Excel under which the code is running. This won't
eliminate the need for changes to your macros, but your code can take
different actions depending on whether the row is there or not.

Hopefully you have this code in just one place, so it's only one routine that
has to be modified. If you don't, maybe this would be a good time to change
all of the macros to call a common procedure rather than duplicating its
actions in multiple places.