combining 1000 separated ranges from multiple sheets into one

[Alaa mg]

Hello

I have about 1000 separated range in each sheet , and each range could contain at least 15 rows so the final rows are about 15000 rows .

every sheet contains name so when put the ranges under each other should create name for each range based on sheet name

the result in COMBINED sheet and will add new sheet before COMBINED sheet every time so should delete data in COMBINED sheet when copy data every time

insert.xlsm
	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		466	0
ALA

insert.xlsm
	A	B	C	D	E
1	DATE	Describe	DEBIT	CREDIT	BALANCE
2	08/01/2022				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	5000	0
MAL

insert.xlsm
	A	B	C	D	E
1	DATE	Describe	DEBIT	CREDIT	BALANCE
2	11/01/2022				0
3	12/01/2022	NOT PAID	1500		1500
4	13/01/2022	NOT PAID	500		2000
5	14/01/2022	PAID	2000	1000	3000
6	15/01/2022	PAID		1010	1990
7	16/01/2022	PAID		1000	990
8	17/01/2022	PAID		990	0
MOUNIRR

I expect like this

insert.xlsm
	A	B	C	D	E
1			NAME
2			ALA
3	DATE	Describe	DEBIT	CREDIT	BALANCE
4	05/01/2022	NOT PAID	1233		1233
5	06/01/2022	NOT PAID	1233		2466
6	07/01/2022	PAID		2000	466
7	08/01/2022	PAID		466	0
8
9
10			NAME
11			MAL
12	DATE	Describe	DEBIT	CREDIT	BALANCE
13	08/01/2022				0
14	09/01/2022	NOT PAID	2000		2000
15	11/01/2022	NOT PAID	2000	1000	3000
16	11/01/2022	NOT PAID	2000	5000	0
17
18
19			NAME
20			MOUNIRR
21	DATE	Describe	DEBIT	CREDIT	BALANCE
22	11/01/2022				0
23	12/01/2022	NOT PAID	1500		1500
24	13/01/2022	NOT PAID	500		2000
25	14/01/2022	PAID	2000	1000	3000
26	15/01/2022	PAID		1010	1990
27	16/01/2022	PAID		1000	990
28	17/01/2022	PAID		990	0
COMBINED

thanks.

 

[Alaa mg]

I will see if I can attach file with big data despite of contain sensitive data , but I think copying random data or maybe repeat the same data to make big .

 

[Alaa mg]

ok
your code is perfect
I tested with about 2000 rows for each sheet . I used about for 4 sheets the code is still fast and gives 0.360sec.
I satisfy your answering .
thank you so much .

 

[igold]

Great- You're welcome, I was happy to help. Thanks for the feedback!