match & copy data amongs sheets and summing repeted

[KalilMe]

hello
I have sheet report should match COLUMN B,C,D for each item A with the others sheets are the same thing COL B,C,D for item A . if they're matched then should insert three columns (PURCHASE, SALES ,BALNCE) and pull the values from the others sheets , and if there repeat the data cross multiple sheets . then should summing the values and if there are new data for each item is existed should add a new data in sheet report before row which contains word TOT as in case1 , and if when run macro again will add a new month and insert three columns (PURCHASE, SALES ,BALNCE) should apply all of conditions in case 2 as I have ever metioned in case 1.
and so on run macro repeatedly should insert three columns and pull the values , summing the values are repeated across all sheets except sheet REPORT and add a new data based on COL B,C,D for each item before row is TOT if the item is existed in sheet report and if there is new item in COL A then should add to the bottom



first

COLLECTION (1) (1) (1).xlsm
	A	B	C	D	E	F
1	CODE	BR	TY	OR	PURCHASE	SALES
2	FR-00	FR	BANANA	TT	200	10
3		FR	APPLE	LL	100	20
4		FR	PEAR	NN	10	-
5		FR	BANANA	QQ	20	-
6	VEG-00	VEG	TOMATO	SS	12	-
7		VEG	TOMATO	AA	12	12
8	FO-00	TUNA	180G	TH	20	5
9		TUNA	180G	IND	10
SAS

second

COLLECTION (1) (1) (1).xlsm
	A	B	C	D	E
1	CODE	BR	TY	OR	SALES
2	FR-00	FR	BANANA	TT	5
3		FR	APPLE	LL	2
4		FR	PEAR	NN	3
5		FR	BANANA	QQ	-
6	VEG-00	VEG	TOMATO	SS	5
SS

third

COLLECTION (1) (1) (1).xlsm
	A	B	C	D	E
1	CODE	BR	TY	OR	PURCHASE
2	FR-00	FR	APPLE	LL	120
3		FR	PEAR	NN	30
4		FR	BANANA	QQ	40
5	VEG-00	VEG	TOMATO	SS	50
6		VEG	TOMATO	AA	5
7		VEG	ONION	AA1	6
8		VEG	POTATO	AA2	7
SR

fourt before result

COLLECTION (1) (1) (1).xlsm
	A	B	C	D
2	CODE	BR	TY	OR
3	FR-00	FR	BANANA	TT
4		FR	APPLE	LL
5		FR	PEAR	NN
6		FR	BANANA	QQ
7		TOT
8	VEG-00	VEG	TOMATO	SS
9		VEG	TOMATO	AA
10		VEG	ONION	AA1
11		TOT
REPORT

result in sheet fourth in two cases
case1

COLLECTION (1) (1) (1).xlsm
	A	B	C	D	E	F	G
1						JAN
2	CODE	BR	TY	OR	PURCHASE	SALES	BALANCE
3	FR-00	FR	BANANA	TT	200	15	185
4		FR	APPLE	LL	220	22	198
5		FR	PEAR	NN	40	3	37
6		FR	BANANA	QQ	60	-
7		TOT			520	40	420
8	VEG-00	VEG	TOMATO	SS	62	5	57
9		VEG	TOMATO	AA	17	17	-
10		VEG	ONION	AA1	6	-	6
11		VEG	POTATO	AA2	7	-	7
12		TOT			92	22	70
13	FO-00	TUNA	180G	TH	20	5	15
14		TUNA	180G	IND	10	0	10
15		TOT			30	5	25
CASE1

Cell Formulas
Range	Formula
G13:G14,G8:G11,G3:G5	G3	=E3-F3
E12:G12,E7:G7	E7	=SUM(E3:E6)
E15:G15	E15	=SUM(E13:E14)

case2

COLLECTION (1) (1) (1).xlsm
	A	B	C	D	E	F	G	H	I	J
1						JAN			FEB
2	CODE	BR	TY	OR	PURCHASE	SALES	BALANCE	PURCHASE	SALES	BALANCE
3	FR-00	FR	BANANA	TT	200	15	185	200	15	370
4		FR	APPLE	LL	220	22	198	220	22	198
5		FR	PEAR	NN	40	3	37	40	3	37
6		FR	BANANA	QQ	60	-		60	-	60
7		TOT			520	40	420	520	40	665
8	VEG-00	VEG	TOMATO	SS	62	5	57	62	5	114
9		VEG	TOMATO	AA	17	17	-	17	17	-
10		VEG	ONION	AA1	6	-	6	6	-	12
11		VEG	POTATO	AA2	7	-	7	7	-	14
12		TOT			92	22	70	92	22	140
13	FO-00	TUNA	180G	TH	20	5	15	20	5	30
14		TUNA	180G	IND	10	0	10	10	0	20
15		TOT			30	5	25	30	5	50
CASE2

Cell Formulas
Range	Formula
J4:J6,G13:G14,G8:G11,G3:G5	G3	=E3-F3
E12:J12,E7:J7	E7	=SUM(E3:E6)
J3,J13:J14,J8:J11	J3	=G3+H3-I3
E15:J15	E15	=SUM(E13:E14)

note: the highlighted by red to see the differnce before and after
if this is not clear please informe me .
thanks

 

[KalilMe]

I hope some body help

 

[bebo021999]

It's hard to do without sample WS. Try to attach link to get it.

 

[KalilMe]

see if it helps
COLLECTION (1) (1) (1).xlsm

 

[bebo021999]

This requires VBA that I am not familiar with. Hope someone can gives help.

 

[KalilMe]

ok I will waiting for another body