Totalling Dynamic Range Subtotals in Excel 2003

[max@SATX]

I have created a VBA function in an Excel 2003 worksheet takes a named range,
copies it, inserts it below the original range and does a re-naming of
specific text in a cell within that range.

I am now attempting to take the sum of a column of cells within the range
and add them to the previous sum of the original cells and total them in a
different named range below all of that. I cannot determine how to add the
two 'sub-totals' into one grand total each time a dynamically created range
is added.

Can someone please assist? Thanks.

 

[Pete_UK]

Use SUBTOTAL(9,range) instead of SUM(range).

Hope this helps.

Pete

 

[max@SATX]

Actually, this helps somewhat, but....it will only total the fist dynamic
range and not subsequent ranges. In other words, the formula carries to a
grand total but when an additional dynamic range is added to the list, it
does not pick up the totals from it. My assumption is that there must be
some kind of 'renaming' effort that needs to happen before the dynamic range
can be copied and then totalled with the other(s). ???

 

[Pete_UK]

What formulas are you using? You could have:

=SUBTOTAL(9,range1)
=SUBTOTAL(9,range2)
=SUBTOTAL(9,range3)

etc. for each range, and then

=SUBTOTAL(9,A:A)

to get the overall range (assuming they are all in column A). Note
that the subtotals don't have to be in the same column, and the last
one won't work if it is in column A.

Hope this helps.

Pete

 

[max@SATX]

Thanks, but this does not answer my dilemma. I am probably not explaining it
very well. When my function runs automatically, it creates a new task
between the first set of tasks and the grand totals. I need the subtotal
function to automatically increase the grand total based upon the addition of
the subtotal from the new task. The question (I think() is, how do I get
Excel to recognize the additional subtotal in the grand total column after
addition of the new task?

 

[Pete_UK]

I can't answer you without further details of your "function" and what
"tasks" it performs.

Pete

 

[max@SATX]

Here is the function in VBA:

Application.Goto Reference:="number"
Dim number As Integer
number = ActiveCell.Value + 1
ActiveCell.FormulaR1C1 = number

Application.Goto Reference:="Task1"
Selection.Copy

Application.Goto Reference:="LaborTotals"
Selection.Insert Shift:=xlDown
--
max@SATX

I can't answer you without further details of your "function" and what
"tasks" it performs.

Pete