copy amount from last row to above cell for adjacent cell into adjacent column

[Mussala]

Hi
I would copy the amount from lastrow(TOTAL) in column E and put above cell in column F for two sheets.

MOVED with code.xlsm
	A	B	C	D	E	F
1	DATE	ITEM	IMPORT	EXPORT	BALANCE	REAL
2	15/06/2023	ITFG-001	2,000.00		2,000.00
3	15/06/2023	ITFG-002	2,200.00	1,000.00	1,200.00
4	15/06/2023	ITFG-003	2,400.00	2,000.00	400.00
5	15/06/2023	ITFG-004	2,600.00		2,600.00
6	TOTAL		9,200.00	3,000.00	6,200.00
7	16/06/2023	ITFG-005	2,800.00		2,800.00
8	16/06/2023	ITFG-006	3,000.00	1,200.00	1,800.00
9	16/06/2023	ITFG-007	3,200.00	200.00	3,000.00
10	16/06/2023	ITFG-008	3,400.00	200.00	3,200.00
11	TOTAL		12,400.00	1,600.00	10,800.00
12	17/06/2023	ITFG-009	3,600.00	500.00	3,100.00
13	TOTAL		3,600.00	500.00	3,100.00
14	18/06/2023	ITFG-010	3,800.00		3,800.00
15	TOTAL		3,800.00	0.00	3,800.00
16	19/06/2023	ITFG-011	4,000.00	500.00	3,500.00
17	19/06/2023	ITFG-012	4,200.00	600.00	3,600.00
18	19/06/2023	ITFG-013	4,400.00	700.00	3,700.00
19	19/06/2023	ITFG-014	4,600.00		4,600.00
20	19/06/2023	ITFG-015	4,800.00		4,800.00
21	19/06/2023	ITFG-016	5,000.00	100.00	4,900.00
22	19/06/2023	ITFG-017	5,200.00		5,200.00
23	TOTAL		32,200.00	1,900.00	30,300.00
24	20/06/2023	ITFG-017	5,200.00		5,200.00
25	TOTAL		5,200.00	0.00	5200
SV

Cell Formulas
Range	Formula
C6:E6	C6	=SUBTOTAL(9,C$2:C5)
C11:E11	C11	=SUBTOTAL(9,C$7:C10)
C13:E13	C13	=SUBTOTAL(9,C$12:C12)
C15:E15	C15	=SUBTOTAL(9,C$14:C14)
C23:E23	C23	=SUBTOTAL(9,C$16:C22)
E24,E16:E22,E14,E12,E7:E10,E2:E5	E2	=C2-D2
C25:E25	C25	=SUBTOTAL(9,C$24:C24)

MOVED with code.xlsm
	A	B	C	D	E	F
1	DATE	ITEM	IMPORT	EXPORT	BALANCE	REAL
2	19/06/2023	ITFG-001	2,000.00		2,000.00
3	19/06/2023	ITFG-002	2,200.00	1,000.00	1,200.00
4	19/06/2023	ITFG-003	2,400.00	2,000.00	400.00
5	19/06/2023	ITFG-004	2,600.00		2,600.00
6	TOTAL		9,200.00	3,000.00	6,200.00
mm

Cell Formulas
Range	Formula
E2:E6	E2	=C2-D2
C6:D6	C6	=SUM(C2:C5)

result

MOVED with code.xlsm
	A	B	C	D	E	F
1	DATE	ITEM	IMPORT	EXPORT	BALANCE	REAL
2	15/06/2023	ITFG-001	2,000.00		2,000.00
3	15/06/2023	ITFG-002	2,200.00	1,000.00	1,200.00
4	15/06/2023	ITFG-003	2,400.00	2,000.00	400.00
5	15/06/2023	ITFG-004	2,600.00		2,600.00	6,200.00
6	TOTAL		9,200.00	3,000.00	6,200.00
7	16/06/2023	ITFG-005	2,800.00		2,800.00
8	16/06/2023	ITFG-006	3,000.00	1,200.00	1,800.00
9	16/06/2023	ITFG-007	3,200.00	200.00	3,000.00
10	16/06/2023	ITFG-008	3,400.00	200.00	3,200.00	10,800.00
11	TOTAL		12,400.00	1,600.00	10,800.00
12	17/06/2023	ITFG-009	3,600.00	500.00	3,100.00	3,100.00
13	TOTAL		3,600.00	500.00	3,100.00
14	18/06/2023	ITFG-010	3,800.00		3,800.00	3,800.00
15	TOTAL		3,800.00	0.00	3,800.00
16	19/06/2023	ITFG-011	4,000.00	500.00	3,500.00
17	19/06/2023	ITFG-012	4,200.00	600.00	3,600.00
18	19/06/2023	ITFG-013	4,400.00	700.00	3,700.00
19	19/06/2023	ITFG-014	4,600.00		4,600.00
20	19/06/2023	ITFG-015	4,800.00		4,800.00
21	19/06/2023	ITFG-016	5,000.00	100.00	4,900.00
22	19/06/2023	ITFG-017	5,200.00		5,200.00	30,300.00
23	TOTAL		32,200.00	1,900.00	30,300.00
24	20/06/2023	ITFG-017	5,200.00		5,200.00	5,200.00
25	TOTAL		5,200.00	0.00	5200
SV

Cell Formulas
Range	Formula
C6:E6	C6	=SUBTOTAL(9,C$2:C5)
C11:E11	C11	=SUBTOTAL(9,C$7:C10)
C13:E13	C13	=SUBTOTAL(9,C$12:C12)
C15:E15	C15	=SUBTOTAL(9,C$14:C14)
C23:E23	C23	=SUBTOTAL(9,C$16:C22)
E24,E16:E22,E14,E12,E7:E10,E2:E5	E2	=C2-D2
C25:E25	C25	=SUBTOTAL(9,C$24:C24)

MOVED with code.xlsm
	A	B	C	D	E	F
1	DATE	ITEM	IMPORT	EXPORT	BALANCE	REAL
2	19/06/2023	ITFG-001	2,000.00		2,000.00
3	19/06/2023	ITFG-002	2,200.00	1,000.00	1,200.00
4	19/06/2023	ITFG-003	2,400.00	2,000.00	400.00
5	19/06/2023	ITFG-004	2,600.00		2,600.00	6,200.00
6	TOTAL		9,200.00	3,000.00	6,200.00
mm

Cell Formulas
Range	Formula
E2:E6	E2	=C2-D2
C6:D6	C6	=SUM(C2:C5)

with considering the TOTAL row will change in location for MM sheet .
just copy to TOTAL row precedes .

thanks.

 

[myall_blues]

Is this what you need? See formula in F2, copied down.

Book1
	A	B	C	D	E	F
1	DATE	ITEM	IMPORT	EXPORT	BALANCE	REAL
2	15/06/2023	ITFG-001	2000		2000
3	15/06/2023	ITFG-002	2200	1000	1200
4	15/06/2023	ITFG-003	2400	2000	400
5	15/06/2023	ITFG-004	2600		2600	6200
6	TOTAL		9200	3000	6200
7	16/06/2023	ITFG-005	2800		2800
8	16/06/2023	ITFG-006	3000	1200	1800
9	16/06/2023	ITFG-007	3200	200	3000
10	16/06/2023	ITFG-008	3400	200	3200	10800
11	TOTAL		12400	1600	10800
12	17/06/2023	ITFG-009	3600	500	3100	3100
13	TOTAL		3600	500	3100
14	18/06/2023	ITFG-010	3800		3800	3800
15	TOTAL		3800	0	3800
16	19/06/2023	ITFG-011	4000	500	3500
17	19/06/2023	ITFG-012	4200	600	3600
18	19/06/2023	ITFG-013	4400	700	3700
19	19/06/2023	ITFG-014	4600		4600
20	19/06/2023	ITFG-015	4800		4800
21	19/06/2023	ITFG-016	5000	100	4900
22	19/06/2023	ITFG-017	5200		5200	30300
23	TOTAL		32200	1900	30300
24	20/06/2023	ITFG-017	5200		5200	5200
25	TOTAL		5200	0	5200
Sheet1

Cell Formulas
Range	Formula
E2:E5,E7:E10,E12,E14,E16:E22,E24	E2	=C2-D2
F2:F25	F2	=IF(A3="TOTAL",E3,"")
C6:E6	E6	=SUBTOTAL(9,E$2:E5)
C11:E11	E11	=SUBTOTAL(9,E$7:E10)
C13:E13	E13	=SUBTOTAL(9,E$12:E12)
C15:E15	E15	=SUBTOTAL(9,E$14:E14)
C23:E23	E23	=SUBTOTAL(9,E$16:E22)
C25:E25	C25	=SUBTOTAL(9,C$24:C24)

 

[Mussala]

Impressive !
I expected doing by vba . formula is small chance to do that.
thank you so much .

 

[myall_blues]

You’re welcome.