Hi Experts ,
I need to efficient way by create really fast macro to deal with 10000 rows contains 500 ranges . each range contains some rows .
in LASTROW sheet I search for range in H2 cell by name to match with column C in SPLITTING sheet then should copy the range .
EXAMPLE
result
if I write another name
will copy to the bottom like this
and if I repeat writing the same name in H2 cell has already copy like this
then should pops message " this name has already existed, you can't repeat copying" and exit without repeat copying the same name.
and if the H2 is empty as like this
then the the result should delete all of ranges like this
thanks
I need to efficient way by create really fast macro to deal with 10000 rows contains 500 ranges . each range contains some rows .
in LASTROW sheet I search for range in H2 cell by name to match with column C in SPLITTING sheet then should copy the range .
EXAMPLE
DIVIDED1.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | ||
2 | 01/01/2022 | - | ABDEND1 | OPENNING | 20,000.00 | 20,000.00 | |||
3 | 04/01/2022 | PA-B3 | ABDEND1 | PA | 20,000.00 | 40,000.00 | |||
4 | 05/01/2022 | SA-B35 | ABDEND1 | SA | 1,000.00 | 39,000.00 | |||
5 | TOTAL | 40,000.00 | 1,000.00 | 39,000.00 | |||||
6 | |||||||||
7 | |||||||||
8 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | ||
9 | 14/01/2022 | PA-B352 | ABDEND10 | PA | 140.00 | 140.00 | |||
10 | 14/01/2022 | PA-B352 | ABDEND10 | PA | 100.00 | 240.00 | |||
11 | TOTAL | 240.00 | 240.00 | ||||||
12 | |||||||||
13 | |||||||||
14 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | ||
15 | 14/04/2022 | PA-B442 | ABDEND100 | PA | 1,040.00 | 1,040.00 | |||
16 | TOTAL | 1,040.00 | 1,040.00 | ||||||
17 | |||||||||
18 | |||||||||
19 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | ||
20 | 30/09/2024 | PA-B1342 | ABDEND1000 | PA | 10,040.00 | 10,040.00 | |||
21 | 30/09/2024 | PA-B1342 | ABDEND1000 | SA | 40.00 | 10,000.00 | |||
22 | 01/10/2024 | SA | 1,000.00 | 9,000.00 | |||||
23 | TOTAL | 10,040.00 | 9,000.00 | ||||||
24 | |||||||||
25 | |||||||||
26 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | ||
27 | 01/10/2024 | PA-B1343 | ABDEND1001 | PA | 10,050.00 | 10,050.00 | |||
28 | TOTAL | 10,050.00 | 10,050.00 | ||||||
splitting |
DIVIDED1.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | SEARCH NAME | |||||||||
2 | ABDEND1 | |||||||||
3 | ||||||||||
4 | ||||||||||
5 | ||||||||||
6 | ||||||||||
7 | ||||||||||
8 | ||||||||||
9 | ||||||||||
10 | ||||||||||
11 | ||||||||||
12 | ||||||||||
13 | ||||||||||
14 | ||||||||||
15 | ||||||||||
16 | ||||||||||
17 | ||||||||||
18 | ||||||||||
19 | ||||||||||
20 | ||||||||||
21 | ||||||||||
22 | ||||||||||
LASTROW |
result
DIVIDED1.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | SEARCH NAME | ||
2 | 01/01/2022 | - | ABDEND1 | OPENNING | 20,000.00 | 20,000.00 | ABDEND1 | |||
3 | 04/01/2022 | PA-B3 | ABDEND1 | PA | 20,000.00 | 40,000.00 | ||||
4 | 05/01/2022 | SA-B35 | ABDEND1 | SA | 1,000.00 | 39,000.00 | ||||
5 | TOTAL | 40,000.00 | 1,000.00 | 39,000.00 | ||||||
LASTROW |
if I write another name
DIVIDED1.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | SEARCH NAME | ||
2 | 01/01/2022 | - | ABDEND1 | OPENNING | 20,000.00 | 20,000.00 | ABDEND10 | |||
3 | 04/01/2022 | PA-B3 | ABDEND1 | PA | 20,000.00 | 40,000.00 | ||||
4 | 05/01/2022 | SA-B35 | ABDEND1 | SA | 1,000.00 | 39,000.00 | ||||
5 | TOTAL | 40,000.00 | 1,000.00 | 39,000.00 | ||||||
LASTROW |
will copy to the bottom like this
DIVIDED1.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | SEARCH NAME | ||
2 | 01/01/2022 | - | ABDEND1 | OPENNING | 20,000.00 | 20,000.00 | ABDEND10 | |||
3 | 04/01/2022 | PA-B3 | ABDEND1 | PA | 20,000.00 | 40,000.00 | ||||
4 | 05/01/2022 | SA-B35 | ABDEND1 | SA | 1,000.00 | 39,000.00 | ||||
5 | TOTAL | 40,000.00 | 1,000.00 | 39,000.00 | ||||||
6 | ||||||||||
7 | ||||||||||
8 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | |||
9 | 14/01/2022 | PA-B352 | ABDEND10 | PA | 140.00 | 140.00 | ||||
10 | 14/01/2022 | PA-B352 | ABDEND10 | PA | 100.00 | 240.00 | ||||
11 | TOTAL | 240.00 | 240.00 | |||||||
12 | ||||||||||
13 | ||||||||||
14 | ||||||||||
15 | ||||||||||
16 | ||||||||||
17 | ||||||||||
18 | ||||||||||
19 | ||||||||||
20 | ||||||||||
21 | ||||||||||
22 | ||||||||||
23 | ||||||||||
LASTROW |
and if I repeat writing the same name in H2 cell has already copy like this
DIVIDED1.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | SEARCH NAME | ||
2 | 01/01/2022 | - | ABDEND1 | OPENNING | 20,000.00 | 20,000.00 | ABDEND1 | |||
3 | 04/01/2022 | PA-B3 | ABDEND1 | PA | 20,000.00 | 40,000.00 | ||||
4 | 05/01/2022 | SA-B35 | ABDEND1 | SA | 1,000.00 | 39,000.00 | ||||
5 | TOTAL | 40,000.00 | 1,000.00 | 39,000.00 | ||||||
6 | ||||||||||
7 | ||||||||||
8 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | |||
9 | 14/01/2022 | PA-B352 | ABDEND10 | PA | 140.00 | 140.00 | ||||
10 | 14/01/2022 | PA-B352 | ABDEND10 | PA | 100.00 | 240.00 | ||||
11 | TOTAL | 240.00 | 240.00 | |||||||
LASTROW |
then should pops message " this name has already existed, you can't repeat copying" and exit without repeat copying the same name.
and if the H2 is empty as like this
DIVIDED1.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | SEARCH NAME | ||
2 | 01/01/2022 | - | ABDEND1 | OPENNING | 20,000.00 | 20,000.00 | ||||
3 | 04/01/2022 | PA-B3 | ABDEND1 | PA | 20,000.00 | 40,000.00 | ||||
4 | 05/01/2022 | SA-B35 | ABDEND1 | SA | 1,000.00 | 39,000.00 | ||||
5 | TOTAL | 40,000.00 | 1,000.00 | 39,000.00 | ||||||
6 | ||||||||||
7 | ||||||||||
8 | DATE | INVOICE NO | CLIENT NO | DESCRIBE | DEBIT | CREDIT | BALANCE | |||
9 | 14/01/2022 | PA-B352 | ABDEND10 | PA | 140.00 | 140.00 | ||||
10 | 14/01/2022 | PA-B352 | ABDEND10 | PA | 100.00 | 240.00 | ||||
11 | TOTAL | 240.00 | 240.00 | |||||||
LASTROW |
then the the result should delete all of ranges like this
DIVIDED1.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | SEARCH NAME | |||||||||
2 | ||||||||||
3 | ||||||||||
4 | ||||||||||
5 | ||||||||||
6 | ||||||||||
7 | ||||||||||
8 | ||||||||||
9 | ||||||||||
10 | ||||||||||
11 | ||||||||||
12 | ||||||||||
13 | ||||||||||
14 | ||||||||||
15 | ||||||||||
16 | ||||||||||
17 | ||||||||||
18 | ||||||||||
19 | ||||||||||
20 | ||||||||||
21 | ||||||||||
22 | ||||||||||
23 | ||||||||||
LASTROW |
thanks