Hello
I search for macro to deal 3000 rows for about 5 sheets for INVOICE file and about 15 sheets CUSTOMERS for CUSTOMER file
INVOICE file contents .
CUSTOMER file contents
so should match NAME in column C for 1,2,3 sheets in INVOICE file with sheet name in CUSTOMERS file then populate data in all of sheets in CUSTOMERS file .
should copy the date from column(B) and INVOICE in column D and TOTAL in column G from the TOTAL (lastrow ) from BUYING sheet in INVOICE file and put DATE,INVOICE in column A,B in CUSTOMERS file and put amount in column D(CREDIT) in CUSTOMERS file ,should copy the date from column(B) and INVOICE in column D and TOTAL in column G from the TOTAL (lastrow ) from SALES sheet in INVOICE file and put DATE,INVOICE in column A,B in CUSTOMERS file and put amount in column C(DEBIT) in CUSTOMERS file , should copy the date from column(B) and INVOICE in column D and TOTAL in column G from the TOTAL (lastrow ) from BUYING RETURN sheet in INVOICE file and put DATE,INVOICE in column A,B in CUSTOMERS file and put amount in column C(DEBIT) in CUSTOMERS file , should copy the date from column(B) and INVOICE in column D and TOTAL in column G from the TOTAL (lastrow ) from SALES RETURN sheet in INVOICE file and put DATE,INVOICE in column A,B in CUSTOMERS file and put amount in column D(CREDIT ) in CUSTOMERS file ,as to VOUCHER sheet should match custmer name in C5 with sheet name in CUSTOMMERS file ,if the cell F4 is SALES thin will put in column C(DEBIT) in CUSTOMMERS FILE and copy DATE,VOUCHER NO from VOUCHER sheet and put in columns A,B
here is result
but there is another case about VOUCHER sheet for INVOICE file , if the cell F4 is BUYING then will put the amount from H6 and put in column D (CREDIT) for CUSTOMERS file like this
result
two files are xlsm extensions , existed in the same folder .
every time I want clear data except formulas for each customer in CUSTOMER file before bring data and should clear cells in VOUCHER sheet when copy to CUSTOMERS file ,also should add formulas when add new data before TOTAL row for each customer as showing in CR-1000 customer in CUSTOMERS files
I want doing the same thing for the rest of customers in CUSTOMERS file by
I hope find this macro by help experts .
thanks
I search for macro to deal 3000 rows for about 5 sheets for INVOICE file and about 15 sheets CUSTOMERS for CUSTOMER file
INVOICE file contents .
| INVOICE.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | ITEM | DATE | NAME | INVOICE | GOODS | TYPE | PR | QTY | UNIT | TOTAL | ||
| 2 | 1 | 26/07/2023 | CR-1000 | STVG-1000 | ATR | AM1 | MTR | 5.00 | 25.00 | 125.00 | ||
| 3 | 2 | 26/07/2023 | CR-1000 | STVG-1000 | ATR | AM2 | PO | 4.00 | 35.00 | 140.00 | ||
| 4 | 3 | 26/07/2023 | CR-1000 | STVG-1000 | ATR | AM1 | SO | 2.00 | 45.00 | 90.00 | ||
| 5 | TOTAL | 26/07/2023 | CR-1000 | STVG-1000 | 355.00 | |||||||
| 6 | 1 | 26/07/2023 | CR-1001 | STVG-1001 | ATR | AM1 | GR | 2.00 | 23.00 | 46.00 | ||
| 7 | 2 | 26/07/2023 | CR-1001 | STVG-1001 | ATR | AM2 | PO | 2.00 | 35.00 | 70.00 | ||
| 8 | TOTAL | 26/07/2023 | CR-1001 | STVG-1001 | 116.00 | |||||||
| 9 | 1 | 27/07/2023 | CR-1000 | STVG-1002 | ATR | AM1 | MTR | 2.00 | 25.00 | 50.00 | ||
| 10 | 2 | 27/07/2023 | CR-1000 | STVG-1002 | ATR | AM2 | PO | 2.00 | 35.00 | 70.00 | ||
| 11 | 3 | 27/07/2023 | CR-1000 | STVG-1002 | ATR | AM1 | SO | 2.00 | 45.00 | 90.00 | ||
| 12 | TOTAL | 27/07/2023 | CR-1000 | STVG-1002 | 210.00 | |||||||
BUYING | ||||||||||||
| INVOICE.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | ITEM | DATE | NAME | INVOICE | GOODS | TYPE | PR | QTY | UNIT | TOTAL | ||
| 2 | 1 | 26/08/2023 | CR-1000 | FRVG-10000 | ATR | AM1 | GR | 10.00 | 10.00 | 100.00 | ||
| 3 | 2 | 26/08/2023 | CR-1000 | FRVG-10000 | ATR | AM2 | PO | 10.00 | 20.00 | 200.00 | ||
| 4 | TOTAL | 26/08/2023 | CR-1000 | FRVG-10000 | 300.00 | |||||||
| 5 | 1 | 26/08/2023 | CR-1001 | FRVG-10001 | ATR | AM1 | GR | 10.00 | 22.00 | 220.00 | ||
| 6 | 2 | 26/08/2023 | CR-1001 | FRVG-10001 | ATR | AM2 | PO | 10.00 | 33.00 | 330.00 | ||
| 7 | TOTAL | 26/08/2023 | CR-1001 | FRVG-10001 | 550.00 | |||||||
| 8 | 1 | 26/08/2023 | CR-1002 | FRVG-10002 | ATR | AM1 | GR | 10.00 | 10.00 | 100.00 | ||
| 9 | 2 | 26/08/2023 | CR-1002 | FRVG-10002 | ATR | AM1 | PO | 20.00 | 10.00 | 200.00 | ||
| 10 | 3 | 26/08/2023 | CR-1002 | FRVG-10002 | ATR | AM4 | QW | 10.00 | 200.00 | 2,000.00 | ||
| 11 | TOTAL | 26/08/2023 | CR-1002 | FRVG-10002 | 2,300.00 | |||||||
| 12 | 1 | 26/08/2023 | CR-1000 | FRVG-10003 | ATR | AM1 | GR | 20.00 | 100.00 | 2,000.00 | ||
| 13 | 2 | 26/08/2023 | CR-1000 | FRVG-10003 | ATR | AM1 | PO | 20.00 | 200.00 | 4,000.00 | ||
| 14 | 3 | 26/08/2023 | CR-1000 | FRVG-10003 | ATR | AM4 | QW | 200.00 | 100.00 | 20,000.00 | ||
| 15 | TOTAL | 26/08/2023 | CR-1000 | FRVG-10003 | 26,000.00 | |||||||
SALES | ||||||||||||
| INVOICE.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | ITEM | DATE | NAME | INVOICE | GOODS | TYPE | PR | QTY | UNIT | TOTAL | ||
| 2 | 1 | 29/07/2023 | CR-1000 | RTSTVG-1000 | ATR | AM1 | SO | 2.00 | 45.00 | 90.00 | ||
| 3 | TOTAL | 29/07/2023 | CR-1000 | RTSTVG-1000 | 90.00 | |||||||
| 4 | 1 | 29/07/2023 | CR-1000 | RTSTVG-1001 | ATR | AM1 | MTR | 2.00 | 25.00 | 50.00 | ||
| 5 | TOTAL | 29/07/2023 | CR-1000 | RTSTVG-1001 | 50.00 | |||||||
BUYING RETURN | ||||||||||||
| INVOICE.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | ITEM | DATE | NAME | INVOICE | GOODS | TYPE | PR | QTY | UNIT | TOTAL | ||
| 2 | 1 | 30/10/2023 | CR-1000 | SRTFRVG-10001 | ATR | AM1 | GR | 10.00 | 22.00 | 220.00 | ||
| 3 | TOTAL | 30/10/2023 | CR-1000 | SRTFRVG-10001 | 220.00 | |||||||
| 4 | 1 | 30/10/2023 | CR-1002 | SRTFRVG-10002 | ATR | AM4 | QW | 10.00 | 200.00 | 2,000.00 | ||
| 5 | 2 | 30/10/2023 | CR-1002 | SRTFRVG-10002 | ATR | AM1 | PO | 20.00 | 10.00 | 200.00 | ||
| 6 | TOTAL | 30/10/2023 | CR-1002 | SRTFRVG-10002 | 2,200.00 | |||||||
SALES RETURNS | ||||||||||||
| INVOICE.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 2 | DATE | VOUCHER NO | ||||||||
| 3 | 27/09/2023 | VTR-110 | INVOICE | |||||||
| 4 | NAME | SALES | ||||||||
| 5 | CR-1000 | TOTAL | ||||||||
| 6 | 300 | |||||||||
VOUCHER | ||||||||||
CUSTOMER file contents
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2,E4 | E2 | =C2-D2 |
| E3 | E3 | =E2+C3-D3 |
| C4:D4 | C4 | =SUM(C2:C3) |
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2,E4 | E2 | =C2-D2 |
| E3 | E3 | =E2+C3-D3 |
| C4:D4 | C4 | =SUM(C2:C3) |
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2,E4 | E2 | =C2-D2 |
| E3 | E3 | =E2+C3-D3 |
| C4:D4 | C4 | =SUM(C2:C3) |
so should match NAME in column C for 1,2,3 sheets in INVOICE file with sheet name in CUSTOMERS file then populate data in all of sheets in CUSTOMERS file .
should copy the date from column(B) and INVOICE in column D and TOTAL in column G from the TOTAL (lastrow ) from BUYING sheet in INVOICE file and put DATE,INVOICE in column A,B in CUSTOMERS file and put amount in column D(CREDIT) in CUSTOMERS file ,should copy the date from column(B) and INVOICE in column D and TOTAL in column G from the TOTAL (lastrow ) from SALES sheet in INVOICE file and put DATE,INVOICE in column A,B in CUSTOMERS file and put amount in column C(DEBIT) in CUSTOMERS file , should copy the date from column(B) and INVOICE in column D and TOTAL in column G from the TOTAL (lastrow ) from BUYING RETURN sheet in INVOICE file and put DATE,INVOICE in column A,B in CUSTOMERS file and put amount in column C(DEBIT) in CUSTOMERS file , should copy the date from column(B) and INVOICE in column D and TOTAL in column G from the TOTAL (lastrow ) from SALES RETURN sheet in INVOICE file and put DATE,INVOICE in column A,B in CUSTOMERS file and put amount in column D(CREDIT ) in CUSTOMERS file ,as to VOUCHER sheet should match custmer name in C5 with sheet name in CUSTOMMERS file ,if the cell F4 is SALES thin will put in column C(DEBIT) in CUSTOMMERS FILE and copy DATE,VOUCHER NO from VOUCHER sheet and put in columns A,B
here is result
| CUSTOMERS.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | DATE | DETAILS | DEBIT | CREDIT | BALANCE | ||
| 2 | 26/07/2023 | STVG-1000 | 355.00 | -355.00 | |||
| 3 | 27/07/2023 | STVG-1002 | 210.00 | -565.00 | |||
| 4 | 26/08/2023 | FRVG-10000 | 300.00 | -265.00 | |||
| 5 | 26/08/2023 | FRVG-10003 | 26,000.00 | 25,735.00 | |||
| 6 | 29/07/2023 | RTSTVG-1000 | 90.00 | 25,825.00 | |||
| 7 | 29/07/2023 | RTSTVG-1001 | 50.00 | 25,875.00 | |||
| 8 | 27/09/2023 | VTR-110 | 300.00 | 26,175.00 | |||
| 9 | 30/10/2023 | SRTFRVG-10001 | 220.00 | 25,955.00 | |||
| 10 | TOTAL | 26,740.00 | 785.00 | 25,955.00 | |||
CR-1000 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2,E10 | E2 | =C2-D2 |
| E3:E9 | E3 | =E2+C3-D3 |
| C10:D10 | C10 | =SUM(C2:C9) |
but there is another case about VOUCHER sheet for INVOICE file , if the cell F4 is BUYING then will put the amount from H6 and put in column D (CREDIT) for CUSTOMERS file like this
| INVOICE.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 2 | DATE | VOUCHER NO | ||||||||
| 3 | 27/11/2023 | STR-556 | INVOICE | |||||||
| 4 | NAME | BUYING | ||||||||
| 5 | CR-1000 | TOTAL | ||||||||
| 6 | 250 | |||||||||
| 7 | ||||||||||
VOUCHER | ||||||||||
result
| CUSTOMERS.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | DATE | DETAILS | DEBIT | CREDIT | BALANCE | ||
| 2 | 26/07/2023 | STVG-1000 | 355.00 | -355.00 | |||
| 3 | 27/07/2023 | STVG-1002 | 210.00 | -565.00 | |||
| 4 | 26/08/2023 | FRVG-10000 | 300.00 | -265.00 | |||
| 5 | 26/08/2023 | FRVG-10003 | 26,000.00 | 25,735.00 | |||
| 6 | 29/07/2023 | RTSTVG-1000 | 90.00 | 25,825.00 | |||
| 7 | 29/07/2023 | RTSTVG-1001 | 50.00 | 25,875.00 | |||
| 8 | 27/09/2023 | VTR-110 | 300.00 | 26,175.00 | |||
| 9 | 30/10/2023 | SRTFRVG-10001 | 220.00 | 25,955.00 | |||
| 10 | 27/11/2023 | STR-556 | 250.00 | 25,705.00 | |||
| 11 | TOTAL | 26,740.00 | 1,035.00 | 25,705.00 | |||
CR-1000 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2,E11 | E2 | =C2-D2 |
| E3:E10 | E3 | =E2+C3-D3 |
| C11:D11 | C11 | =SUM(C2:C10) |
two files are xlsm extensions , existed in the same folder .
every time I want clear data except formulas for each customer in CUSTOMER file before bring data and should clear cells in VOUCHER sheet when copy to CUSTOMERS file ,also should add formulas when add new data before TOTAL row for each customer as showing in CR-1000 customer in CUSTOMERS files
I want doing the same thing for the rest of customers in CUSTOMERS file by
I hope find this macro by help experts .
thanks
