PDA

View Full Version : Subtotal calculation using Macro



sntnknd
05-20-2008, 12:02 AM
Hello,

I have been trying to create an excel macro that should do a subtotal of some selected fields in my worksheet. The worksheet table looks something like:

CLIENT ORDER NO SALES H1 SALES H2
abcd 1234 100 300
abcd 1235 200 400
abcd 1240 500 100
mnop 1212 600 700
wxyz 1300 100 200
wxyz 1315 300 400
wxyz 1325 500 600
wxyz 1335 700 800

What the macro should do is to do a subtotal of the sales in H1 and H2 against each of these clients (abcd, mnop, wxyz). That is, the output of the macro should be like:

CLIENT SALES H1 SALES H2
abcd 800 800
mnop 600 700
wxyz 1600 2000

I know how to do it manually using the SUBTOTAL function, but I need to implement this feature in a macro, which I am not able to do. Could anyone kindly provide me with some assistance please.

Cheers.

Bob Phillips
05-20-2008, 01:32 AM
Columns("A:D").Subtotal GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(3, 4), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2