VBA - combine multiple worksheets and sum totals

[wibni]

Hi,

I'm using Excel 2003.
I have an Excel File with 12 Sheets named P1 to P12.
Is it possible to combine these sheets into an additional sheet and summing up the individual totals to a grand total?

If Cells A to G from one sheet, matches the other sheet, then copy range (A:G) and sum up the totals to a grand total in column H.

I think I can figure out how to copy from one sheet to another but am not sure how to compare a range of cells from different sheets.

I've copied 2 sheets as an example below.
Any help is much appreciated.

Sheet P1:

Excel Workbook
	A	B	C	D	E	F	G	H
1	AcctID	SubCategory	Country	AccSegment	ManCo	RigSite	Site Jan	Total
2	10-4110-008	Revenue	10	4110	10	008	GGM	-165,284
3	10-4110-024	Revenue	10	4110	10	024	GGM	-226,452
4	10-4420-021	Revenue	10	4420	10	021	NMM	0
5	10-4420-NMM	Revenue	10	4420	10	NMM	NMM	0
6	10-4430-008	Revenue	10	4430	10	008	GGM	-12,616
7	10-4430-029	Revenue	10	4430	10	029	GGM	-263
8	10-4430-066	Revenue	10	4430	10	066	N\A	-2,576
9	10-4430-073	Revenue	10	4430	10	073	N\A	0
10	10-4430-005	Revenue	10	4430	10	005	NMM	-7,482
11	10-5111-008	Direct Expenses	10	5111	10	008	GGM	13,952
12	10-5111-028	Direct Expenses	10	5111	10	028	GGM	8,487
13	10-5111-029	Direct Expenses	10	5111	10	029	GGM	4,575
14	10-5111-038	Direct Expenses	10	5111	10	038	GGM	4,492
P1

Sheet P2:

Excel Workbook
	A	B	C	D	E	F	G	H
1	AcctID	SubCategory	Country	AccSegment	ManCo	RigSite	Site Feb	Total
2	10-4110-008	Revenue	10	4110	10	008	GGM	-158,815
3	10-4110-066	Revenue	10	4110	10	066	GGM	-177,535
4	10-4110-073	Revenue	10	4110	10	073	GGM	-191,822
5	10-4110-029	Revenue	10	4110	10	029	N\A	0
6	10-4110-048	Revenue	10	4110	10	048	N\A	0
7	10-4110-049	Revenue	10	4110	10	049	N\A	0
8	10-4110-005	Revenue	10	4110	10	005	NMM	-142,615
9	10-4110-032	Revenue	10	4110	10	032	NMM	-205,194
10	10-4110-042	Revenue	10	4110	10	042	NMM	-184,471
11	10-4420-008	Revenue	10	4420	10	008	GGM	-6,335
12	10-4420-024	Revenue	10	4420	10	024	GGM	-37,950
13	10-4420-028	Revenue	10	4420	10	028	GGM	-18,282
14	10-4420-055	Revenue	10	4420	10	055	GGM	-6,335
P2

 

[VoG]

This should help you to combine the sheets http://msdn.microsoft.com/en-us/library/cc793964.aspx

 

[wibni]

Thank you for the link.

As I said, I can figure out how to copy data from 1 sheet to a summary sheet - and I think this is what the link explains as well.

What I'm not sure about is how to compare each sheet with another and sum up the rows which are identical for range(A:G).

 

[VoG]

Can't you just total based upon AcctID?

 

[wibni]

Good point, but unfortunately not.
It would have to be summed up based on at least AcctID (A) and RigSite (F).

 

[VoG]

Try like this

=SUMPRODUCT(--(A1:A1000=""10-4110-008"),--(F1:F1000="008"),H1:H1000)

You can replace the literals like "008" with cell references.

 

[wibni]

Thank you.
I'll see whether I can translate that into VBA.