Subtotal calculation using Macro

[sntnknd]

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

[royUK]

Use a PivotTable, you don't need a macro.

[sntnknd]

Use a PivotTable, you don't need a macro.

Hello royUK

I tried to get this done using pivot tables, but somehow I am not able to. The main problem I am having is to arrange Sales H1 and Sales H2 side-by-side. If I add Sales H1, I am not able to add Sales H2 next to it.

I must admit that I have not used pivot tables that way, so this apparently simple task looks like a show-stopper for me. Could you kindly give me some suggestion please.

Oh... by the way, I am using Excel 2003.

Thank you for your help.

Cheers.

[royUK]

Can you attach an example workbook?

[sntnknd]

Can you attach an example workbook?

Hello royUK,

Sorry for this delayed response. I have attached a sample file for your kind reference.

My exact problem is a bit strange and seems to have something to do with the changed behaviour of pivot tables in excel 2007 as compared to Excel 2003. The attached workbook is created in Excel 2007 and contains the base table on top and below it the designed pivot table.

This table does actually work if you have Excel 2007 - no issues at all. But if I share it with someone having Excel 2003, the table doesn't work (I mean it doesn't refresh). I was not sure in which forum should I had to raise this issue, so I did it here. Could you kindly suggest how to resolve this or let me know where could I raise this please.

Thank you for your help.

Cheers.