Sum on Dynamic Data Range - Macro

[cfatz1]

I am writing a VBA macro and have come across something I cannot figure
out. I have a spreadsheet and in column B is the product ID field.
There are 3 different product ID's and each of the products in column A
are given an ID based on a VLookup. At each change in the product ID
in column B, the macro adds a row to the spreadsheet and names that row
based on the product ID. Column C has the amount of sales for each
product category....and I want to have a total of all sales by product
ID in the new row that is added. Here is an example

Writing Tools (Prod ID) Sales
Pens 1.00
Pencils 2.00
Highliters 3.00
6.00 (This is where I need a
dynamic Sum!)
Paper (Prod ID)
Notebooks
Loose Leaf
Printer
(Another Dynamic Sum is needed
here....)
Misc (Prod ID)
Paper Clips
Tacks
Etc.........

The problem I am having is each time this file is saved the number of
products for each of the 3 product ID's may or may not change. How can
I tell Excel to only add the specific # of cells for that specific
category each time the macro is run?? Hope this makes some sort of
sense! Any help is MUCH appreciated!!

-Chris

 

[Nigel]

Why not use either subtotals or a pivot table - both can be made dynamic