Hi
I I no know if macro could deal with 6000 rows for each individual sheet when match sheet names "customers with two sheets " BUYING,SALES" for column name(C).
so in REPORT sheet should populate as I did it.
I want populating the customer name and the invoice number in columns B,C bases on sheets BUYING ,SALES also should populate amounts under BUYING AMOUNT,SALES AMOUNT IN columns D,E based what are existed in BUYING ,SALES sheets in TOTAL row for column J .as to populate amounts in columns F,G from customers sheet CR-1000,CR-1001 then should match sheet name for customers with column NAME (C) for BUYING ,SALES sheets and match invoice number based on last part in column B for customers sheets with INVOICE NO column for BUYING ,SALES sheets and brings values from CREDIT & DEBIT columns in customers sheets .
if there is customers contains amounts are not existed in customers sheets then should also add it in report sheet to compare it after that should calculate in columns H,I as I did .
every time will add new customers sheets and add new data in BUYING ,SALES sheets so should clear cells in report sheet
before bring data when run the macro every time.
before brings data
result should be
I hope somebody help .
I I no know if macro could deal with 6000 rows for each individual sheet when match sheet names "customers with two sheets " BUYING,SALES" for column name(C).
so in REPORT sheet should populate as I did it.
I want populating the customer name and the invoice number in columns B,C bases on sheets BUYING ,SALES also should populate amounts under BUYING AMOUNT,SALES AMOUNT IN columns D,E based what are existed in BUYING ,SALES sheets in TOTAL row for column J .as to populate amounts in columns F,G from customers sheet CR-1000,CR-1001 then should match sheet name for customers with column NAME (C) for BUYING ,SALES sheets and match invoice number based on last part in column B for customers sheets with INVOICE NO column for BUYING ,SALES sheets and brings values from CREDIT & DEBIT columns in customers sheets .
if there is customers contains amounts are not existed in customers sheets then should also add it in report sheet to compare it after that should calculate in columns H,I as I did .
every time will add new customers sheets and add new data in BUYING ,SALES sheets so should clear cells in report sheet
before bring data when run the macro every time.
KLL.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | ITEM | DATE | NAME | INVOICE | GOODS | TYPE | PR | QTY | UNIT | TOTAL | ||
2 | 1 | 25/05/2023 | CR-1000 | FRVG-1000 | ATR | AM1 | GR | 55.00 | 22.00 | 1,210.00 | ||
3 | 2 | 25/05/2023 | CR-1000 | FRVG-1000 | ATR | AM2 | PO | 14.00 | 33.00 | 462.00 | ||
4 | 3 | 25/05/2023 | CR-1000 | FRVG-1000 | ATR | AM1 | SO | 10.00 | 44.00 | 440.00 | ||
5 | TOTAL | 25/05/2023 | CR-1000 | FRVG-1000 | 2,112.00 | |||||||
6 | 1 | 25/05/2023 | CR-1001 | FRVG-1001 | ATR | AM1 | GR | 10.00 | 22.00 | 220.00 | ||
7 | 2 | 25/05/2023 | CR-1001 | FRVG-1001 | ATR | AM2 | PO | 10.00 | 33.00 | 330.00 | ||
8 | TOTAL | 25/05/2023 | CR-1001 | FRVG-1001 | 550.00 | |||||||
9 | 1 | 25/05/2023 | CR-1000 | FRVG-1002 | ATR | AM1 | GR | 55.00 | 22.00 | 1,210.00 | ||
10 | TOTAL | 25/05/2023 | CR-1000 | FRVG-1002 | 1,210.00 | |||||||
11 | 1 | 25/05/2023 | CR-1000 | FRVG-1003 | ATR | AM1 | GR | 55.00 | 10.00 | 550.00 | ||
12 | TOTAL | 25/05/2023 | CR-1000 | FRVG-1003 | 550.00 | |||||||
BUYING |
KLL.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | ITEM | DATE | NAME | INVOICE | GOODS | TYPE | PR | QTY | UNIT | TOTAL | ||
2 | 1 | 25/05/2023 | CR-1000 | STVG-1000 | ATR | AM1 | MTR | 5.00 | 25.00 | 125.00 | ||
3 | 2 | 25/05/2023 | CR-1000 | STVG-1000 | ATR | AM2 | PO | 4.00 | 35.00 | 140.00 | ||
4 | 3 | 25/05/2023 | CR-1000 | STVG-1000 | ATR | AM1 | SO | 2.00 | 45.00 | 90.00 | ||
5 | TOTAL | 25/05/2023 | CR-1000 | STVG-1000 | 355.00 | |||||||
6 | 1 | 25/05/2023 | CR-1001 | STVG-1001 | ATR | AM1 | GR | 2.00 | 23.00 | 46.00 | ||
7 | 2 | 25/05/2023 | CR-1001 | STVG-1001 | ATR | AM2 | PO | 2.00 | 35.00 | 70.00 | ||
8 | TOTAL | 25/05/2023 | CR-1001 | STVG-1001 | 116.00 | |||||||
9 | 1 | 25/05/2023 | CR-1001 | STVG-1002 | ATR | AM1 | GR | 2.00 | 23.00 | 46.00 | ||
10 | TOTAL | 25/05/2023 | CR-1001 | STVG-1002 | 46 | |||||||
SALES |
KLL.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | DATE | INFORMATION | DEBIT | CREDIT | BALANCE | ||
2 | 25/05/2023 | BUYING GOODS INVOICE NUMBER FRVG-1000 | 2112 | -2112 | |||
3 | 25/05/2023 | BUYING GOODS INVOICE NUMBER FRVG-1002 | 1200 | -3312 | |||
4 | 25/05/2023 | SALES GOODS INVOICE NUMBER STVG-1000 | 365 | -2947 | |||
5 | TOTAL | 365 | 3312 | -2947 | |||
CR-1000 |
KLL.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | DATE | INFORMATION | DEBIT | CREDIT | BALANCE | ||
2 | 25/05/2023 | BUYING GOODS INVOICE NUMBER FRVG-1001 | 545 | -545 | |||
3 | 25/05/2023 | SALES GOODS INVOICE NUMBER STVG-1001 | 116 | -429 | |||
4 | 25/05/2023 | SALES GOODS INVOICE NUMBER STVG-1002 | 46 | -383 | |||
5 | TOTAL | 162 | 545 | -383 | |||
CR-1001 |
before brings data
KLL.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | ITEM | NAME | INVOICE NO | BUYING AMOUNT | SALES AMOUNT | CREDIT | DEBIT | DIFFERENCE CREDIT | DIFFERENCE DEBIT | ||
2 | |||||||||||
3 | |||||||||||
4 | |||||||||||
5 | |||||||||||
6 | |||||||||||
7 | |||||||||||
8 | |||||||||||
REPORT |
result should be
KLL.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | ITEM | NAME | INVOICE NO | BUYING AMOUNT | SALES AMOUNT | CREDIT | DEBIT | DIFFERENCE CREDIT | DIFFERENCE DEBIT | ||
2 | 1 | CR-1000 | FRVG-1000 | 2,112.00 | 2,112.00 | 0.00 | 0.00 | ||||
3 | 2 | CR-1000 | FRVG-1002 | 1,210.00 | 1,200.00 | 10.00 | 0.00 | ||||
4 | 3 | CR-1000 | FRVG-1003 | 550.00 | 550.00 | 0.00 | |||||
5 | 4 | CR-1000 | STVG-1000 | 355.00 | 365.00 | 0.00 | -10.00 | ||||
6 | 5 | CR-1001 | FRVG-1001 | 550.00 | 545.00 | 5.00 | 0.00 | ||||
7 | 6 | CR-1001 | STVG-1001 | 116.00 | 116.00 | 0.00 | 0.00 | ||||
8 | 7 | CR-1001 | STVG-1002 | 46.00 | 46.00 | 0.00 | 0.00 | ||||
REPORT |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H2:I8 | H2 | =D2-F2 |
I hope somebody help .
Last edited: