Create list by combining for specific tab contains colors

[Alaa mg]

Hello

I give this sample . each sheet could be 2000 rows and every time I will add new tab and highlight by red & yellows color , I want combining sheets contains red color tab alone and yellows color tab alone

so the result in red color tab by sum for each sheet alone based on sheet name .

red sheet should brings name in column C based on sheet name and sum the whole columns(DEBIT,CREDIT ) and in column BALANCE should subtract DEBIT from CREDIT for each sheet alone and insert TOTAL row to sum the TOTAL for whole names as I put the formulas , should clear data in red & yellows sheets when every time run the macro before brings data again and the same thing for Yellows sheet like RED sheet .

aq
	A	B	C	D	E
1	DATE	Describe	DEBIT	CREDIT	BALANCE
2	05/01/2022	NOT PAID	1233		1233
3	06/01/2022	NOT PAID	1233		2466
4	07/01/2022	PAID		2000	466
5	08/01/2022	PAID		400	66
ALA

aq
	A	B	C	D	E
1	DATE	Describe	DEBIT	CREDIT	BALANCE
2	08/01/2022	OPENING			0
3	09/01/2022	NOT PAID	2000		2000
4	11/01/2022	NOT PAID	2000	1000	3000
5	11/01/2022	NOT PAID	2000	4000	1000
ALN

aq
	A	B	C	D	E
1	DATE	Describe	DEBIT	CREDIT	BALANCE
2	08/01/2022	OPENING			5000
3	09/01/2022	NOT PAID	6000		11000
4	11/01/2022	NOT PAID	2000	1000	12000
5	11/01/2022	NOT PAID	2000	4000	10000
6	12/01/2022	PAID		3000	7000
7	13/01/2022	PAID		2000	5000
8	14/01/2022	PAID		1000	4000
9	15/01/2022	PAID		3000	1000
AMN

aq
	A	B	C	D	E
1	DATE	Describe	DEBIT	CREDIT	BALANCE
2	08/01/2022	OPENING			3000
3	09/01/2022	NOT PAID	10000		13000
4	11/01/2022	NOT PAID	3000		16000
5	11/01/2022	NOT PAID	2000		18000
6	12/01/2022	PAID		1000	17000
7	13/01/2022	PAID		8000	9000
8	14/01/2022	PAID		5000	4000
9	15/01/2022	PAID		3000	1000
MNA

result

aq
	A	B	C	D	E
1	ITEM	NAME	DEBIT	CREDIT	BALANCE
2	1	ALA	2466	2400	66
3	2	ALN	6000	5000	1000
4	TOTAL		8466	7400	1066
RED

Cell Formulas
Range	Formula
E2,E4	E2	=C2-D2
C4:D4	C4	=SUM(C2:C3)

aq
	A	B	C	D	E
1	ITEM	NAME	DEBIT	CREDIT	BALANCE
2	1	AMN	10000	14000	-4000
3	2	MNA	15000	17000	-2000
4	TOTAL		25000	31000	-6000
YELLOWS

Cell Formulas
Range	Formula
E2:E4	E2	=C2-D2
C4:D4	C4	=SUM(C2:C3)

and see the picture Tab color to understand because XL2BB doesn't show