# Totalling Dynamic Range Subtotals in Excel 2003

M

#### 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

P

Hope this helps.

Pete

M

#### 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). ???

P

#### 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

M

#### 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

P

#### Pete_UK

I can't answer you without further details of your "function" and what

Pete

M

#### max@SATX

Here is the function in VBA:

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

Selection.Copy

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

Pete_UK said:
I can't answer you without further details of your "function" and what