macro insert three columns repeatedly with all values based on multiple sheets

[KalilMe]

hi
I have many sheets contain data and values for sales and purchase
in sheet report has also data . what I want insert three columns with the same borders and formatting contain (PURCHASE,SALES,BALANCE) and summing the values for SALES , PURCHASE across multiple sheets and put the values in sheet REPORT in COLUMN SALES & PURCHASE . the COL BALANCE should add the formula as I put in sheet CASE1 when run the macro from the first time but when I run again . the formula will be change as I put in COL BALANCE in sheet CASE2 and somtimes there are some a new items in all the sheets except the sheet REPORT then should add it as I highlighted in sheet REPORT . when get the values after summing the values across sheets should match COL B,C,D toghether and when add a new items in sheet REPORT should match COL A with the others sheets in COL A
NOTE: the sheet CASE1,2 just to understand what I want . the result should be in sheet REPORT and the formula in CASE2 it will continue every time run the macro .
also issued in this forum macro insert three columns repeatedly with all values based on multiple sheets

first sheet

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

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

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

before run the macro how was it in this sheet

COLLECTION.xlsm
	A	B	C	D
1
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

the expected result in sheet REPORT for two cases
case1

COLLECTION.xlsm
	A	B	C	D	E	F	G
1						JAN
2	CODE	BR	TY	OR	PURCHASE	SALES	BALANCE
3	FR-00	FR	BANANA	TT	320	15	305
4		FR	APPLE	LL	100	22	78
5		FR	PEAR	NN	40	3	37
6		FR	BANANA	QQ	60	-
7		TOT			520	40	420
8	VEG-00	VEG	TOMATO	SS	67	5	62
9		VEG	TOMATO	AA	17	12	5
10		VEG	ONION	AA1	6	-	6
11		VEG	POTATO	AA2	7	-	7
12		TOT			97	17	80
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)

case 2

COLLECTION.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	320	15	305	320	15	610
4		FR	APPLE	LL	100	22	78	100	22	156
5		FR	PEAR	NN	40	3	37	40	3	74
6		FR	BANANA	QQ	60	-	60	60	-	120
7		TOT			520	40	480	520	40	960
8	VEG-00	VEG	TOMATO	SS	67	5	62	67	5	124
9		VEG	TOMATO	AA	17	12	5	17	12	10
10		VEG	ONION	AA1	6	-	6	6	-	12
11		VEG	POTATO	AA2	7	-	7	7	-	14
12		TOT			97	17	80	97	17	160
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
E12:J12,E7:J7	E7	=SUM(E3:E6)
G13:G14,G8:G11,G3:G6	G3	=E3-F3
J13:J14,J8:J11,J3:J6	J3	=G3+H3-I3
E15:J15	E15	=SUM(E13:E14)

I hope finding answer in this forum despite this project is very coplicated