Hello,
I would arrange data again by add new sheet "Arra"
this is the original data
the result
without forgetting auto fill name in column B based on D7 and recalculate formulas in column BALANCE and TOTAL row , but I don't need it as in ACCA sheet..every time when run the macro should add new sheet with date today like this Arra_14-11-2024.
thanks
I would arrange data again by add new sheet "Arra"
this is the original data
| KshfHsab1.xls | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ||||||||||
| 2 | ||||||||||
| 3 | ||||||||||
| 4 | ||||||||||
| 5 | ACCOUNT LIST | |||||||||
| 6 | ||||||||||
| 7 | BALANCE: CREDIT (85.00) | TO:FATTUH | ||||||||
| 8 | ||||||||||
| 9 | DATE | VOUCHER NO | INVOICE NO | DESCRIBE | BALANCE | CREDIT | DEBIT | |||
| 10 | 2023.12.31 | BALANCE DAY 2023/12/31 | -41430.000 | 0.00 | 41,430.00 | |||||
| 11 | 2024.01.09 | 489 | RECEIVED VOUCHER | -23430.000 | 18,000.00 | 0.00 | ||||
| 12 | 2024.01.20 | 145 | PURCHASES | -20180.000 | 3,250.00 | 0.00 | ||||
| 13 | 2024.01.30 | 548 | RECEIVED VOUCHER | 5.000 | 20,185.00 | 0.00 | ||||
| 14 | 2024.04.24 | 817 | SALES | -26995.000 | 0.00 | 27,000.00 | ||||
| 15 | 2024.04.24 | 771 | RECEIVED VOUCHER | 5.000 | 27,000.00 | 0.00 | ||||
| 16 | 2024.04.29 | 846 | SALES | -23595.000 | 0.00 | 23,600.00 | ||||
| 17 | 2024.06.02 | 937 | RECEIVED VOUCHER | 1405.000 | 25,000.00 | 0.00 | ||||
| 18 | 2024.06.02 | 996 | SALES | -57995.000 | 0.00 | 59,400.00 | ||||
| 19 | 2024.06.03 | 939 | RECEIVED VOUCHER | -32995.000 | 25,000.00 | 0.00 | ||||
| 20 | 2024.07.06 | 1008 | RECEIVED VOUCHER | 0.000 | 32,995.00 | 0.00 | ||||
| 21 | 2024.07.13 | 1107 | SALES | -27000.000 | 0.00 | 27,000.00 | ||||
| 22 | 2024.07.13 | 1031 | RECEIVED VOUCHER | 0.000 | 27,000.00 | 0.00 | ||||
| 23 | 2024.08.11 | 970000078 | SALES | -4150.000 | 0.00 | 4,150.00 | ||||
| 24 | 2024.08.15 | 970000117 | SALES | -8300.000 | 0.00 | 4,150.00 | ||||
| 25 | 2024.08.17 | 970000121 | SALES | -29900.000 | 0.00 | 21,600.00 | ||||
| 26 | 2024.08.17 | 910000216 | RECEIVED VOUCHER | -29550.000 | 350.00 | 0.00 | ||||
| 27 | 2024.08.29 | 910000217 | RECEIVED VOUCHER | -9550.000 | 20,000.00 | 0.00 | ||||
| 28 | 2024.09.05 | 970000273 | SALES | -13850.000 | 0.00 | 4,300.00 | ||||
| 29 | 2024.09.23 | 910000335 | RECEIVED VOUCHER | 85.000 | 13,935.00 | 0.00 | ||||
| 30 | TOTAL | 85.000 | 212,715.00 | 212,630.00 | ||||||
| 31 | ||||||||||
ACCA | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E12 | E12 | =E22-20180 |
the result
| KshfHsab1.xls | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ||||||||||
| 2 | ||||||||||
| 3 | ||||||||||
| 4 | ||||||||||
| 5 | ACCOUNT LIST | |||||||||
| 6 | ||||||||||
| 7 | BALANCE: CREDIT (85.00) | |||||||||
| 8 | ||||||||||
| 9 | DATE | NAME | DESCRIBE | INVOICE NO | VOUCHER NO | DEBIT | CREDIT | BALANCE | ||
| 10 | 2023.12.31 | FATTUH | BALANCE DAY 2023/12/31 | 41,430.00 | 0.00 | 41430.000 | ||||
| 11 | 2024.01.09 | FATTUH | RECEIVED VOUCHER | 489 | 0.00 | 18,000.00 | 23430.000 | |||
| 12 | 2024.01.20 | FATTUH | PURCHASES | 145 | 0.00 | 3,250.00 | 20180.000 | |||
| 13 | 2024.01.30 | FATTUH | RECEIVED VOUCHER | 548 | 0.00 | 20,185.00 | -5.000 | |||
| 14 | 2024.04.24 | FATTUH | SALES | 817 | 27,000.00 | 0.00 | 26995.000 | |||
| 15 | 2024.04.24 | FATTUH | RECEIVED VOUCHER | 771 | 0.00 | 27,000.00 | -5.000 | |||
| 16 | 2024.04.29 | FATTUH | SALES | 846 | 23,600.00 | 0.00 | 23595.000 | |||
| 17 | 2024.06.02 | FATTUH | RECEIVED VOUCHER | 937 | 0.00 | 25,000.00 | -1405.000 | |||
| 18 | 2024.06.02 | FATTUH | SALES | 996 | 59,400.00 | 0.00 | 57995.000 | |||
| 19 | 2024.06.03 | FATTUH | RECEIVED VOUCHER | 939 | 0.00 | 25,000.00 | 32995.000 | |||
| 20 | 2024.07.06 | FATTUH | RECEIVED VOUCHER | 1008 | 0.00 | 32,995.00 | 0.000 | |||
| 21 | 2024.07.13 | FATTUH | SALES | 1107 | 27,000.00 | 0.00 | 27000.000 | |||
| 22 | 2024.07.13 | FATTUH | RECEIVED VOUCHER | 1031 | 0.00 | 27,000.00 | 0.000 | |||
| 23 | 2024.08.11 | FATTUH | SALES | 970000078 | 4,150.00 | 0.00 | 4150.000 | |||
| 24 | 2024.08.15 | FATTUH | SALES | 970000117 | 4,150.00 | 0.00 | 8300.000 | |||
| 25 | 2024.08.17 | FATTUH | SALES | 970000121 | 21,600.00 | 0.00 | 29900.000 | |||
| 26 | 2024.08.17 | FATTUH | RECEIVED VOUCHER | 910000216 | 0.00 | 350.00 | 29550.000 | |||
| 27 | 2024.08.29 | FATTUH | RECEIVED VOUCHER | 910000217 | 0.00 | 20,000.00 | 9550.000 | |||
| 28 | 2024.09.05 | FATTUH | SALES | 970000273 | 4,300.00 | 0.00 | 13850.000 | |||
| 29 | 2024.09.23 | FATTUH | RECEIVED VOUCHER | 910000335 | 0.00 | 13,935.00 | -85.000 | |||
| 30 | TOTAL | 212,630.00 | 212,715.00 | -85.000 | ||||||
Arra | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H10,H30 | H10 | =F10-G10 |
| H11:H29 | H11 | =H10+F11-G11 |
| F30:G30 | F30 | =SUM(F10:F29) |
without forgetting auto fill name in column B based on D7 and recalculate formulas in column BALANCE and TOTAL row , but I don't need it as in ACCA sheet..every time when run the macro should add new sheet with date today like this Arra_14-11-2024.
thanks
