VBA to copy and rework into a new workbook and save automatically

[ec4Excel2]

Dear Experts,

I have a file downloaded from the bank and we need to "rework" on the data before we can use it to import into another software.

1st Step: We need to change column A into a Date format.

2nd Step: We need to combine the Withdrawals and Deposits into 1 column but both the data on Column C and D have a spacing after the last character.

3rd Step: We need to put a "-" on the Withdrawals and make it red when in 1 column.

Therefore, we need to create the data on column F and G before we can copy and paste into the new workbook book (I already have a formula on Column F and G).

In the new workbook:

1. To copy Date, Amount and Description into a new workbook
2. Desired format to sort the date from 1st to 30th and not 30th to 1st
3. To Highlight the negative figures using Red Fonts
4. To name, save and close the desired result file as "CITI mmm yyyy" as per the date in column A, where CITI will be fixed. That is to save the workbook as per the month and year according to the date in Column A with the bank name, eg: CITI May 2021
5. Closed the "Source from internet file" without saving where this source file name will keep changing from time to time.

Thank you.

My Two Formulas on Sheet 1 column F and G are: To convert the date into a date "=TEXT(LEFT(A10,LEN(A10)-1),"d/mm/yyyy")" To combine the two figures on Column C and D into 1 column "=IF(C10<>"","-"&LEFT(C10,LEN(C10)-1),LEFT(D10,LEN(D10)-1))"

Appreciate your help to make this into a VBA.

Source from Internet.xlsm
	A	B	C	D	E	F	G
1
2
3
4
5
6
7
8
9	Date	Transaction Description	Withdrawals	Deposits		Date	1 Column
10	30 Jun 2021	INW CLR 459223	30,000.00			30/06/2021	-30,000.00
11	30 Jun 2021	INW CLR 469419	3,226.05			30/06/2021	-3,226.05
12	30 Jun 2021	INW CLR 459217	585.00			30/06/2021	-585.00
13	30 Jun 2021	INW CLR 469436	235.40			30/06/2021	-235.40
14	30 Jun 2021	INW CLR 469421	233.26			30/06/2021	-233.26
15	30 Jun 2021	INW CLR 469430	173.26			30/06/2021	-173.26
16	30 Jun 2021	INW CLR 469431	133.75			30/06/2021	-133.75
17	30 Jun 2021	COLL wgcWNCTUn0l	252.00			30/06/2021	-252.00
18	30 Jun 2021	PADD 1254 CUSTOMS	2,997.54			30/06/2021	-2,997.54
19	29 Jun 2021	OTHR S622476923 PAYMENT		16,712.77		29/06/2021	16,712.77
20	29 Jun 2021	GOVT Jobs Support Scheme IRAS		83,476.00		29/06/2021	83,476.00
21	29 Jun 2021	CL CHQ DEP		57,358.50		29/06/2021	57,358.50
22	29 Jun 2021	COLL 1154776 CO LTD	6,853.84			29/06/2021	-6,853.84
23	29 Jun 2021	PADD 785380I FUNDS	220.39			29/06/2021	-220.39
24	29 Jun 2021	PADD IG469Q CHARGES	873.45			29/06/2021	-873.45
25	29 Jun 2021	COLL S6222708SRP ELECTRONICS	54.27			29/06/2021	-54.27
26	29 Jun 2021	PADD I79646Q FEES	873.45			29/06/2021	-873.45
27	29 Jun 2021	PADD I79646Q FEES		16,712.77		29/06/2021	16,712.77
28	29 Jun 2021	Fund Support		83,476.00		29/06/2021	83,476.00
29	29 Jun 2021	CL CHQ DEP		57,358.50		29/06/2021	57,358.50
30	28 Jun 2021	INW CLR 458898	6,907.92			28/06/2021	-6,907.92
31	28 Jun 2021	INW CLR 459218	3,959.00			28/06/2021	-3,959.00
32	28 Jun 2021	INW CLR 469452	2,800.00			28/06/2021	-2,800.00
33	28 Jun 2021	INW CLR 469451	1,900.00			28/06/2021	-1,900.00
34	28 Jun 2021	INW CLR 469441	682.00			28/06/2021	-682.00
35	28 Jun 2021	INW CLR 469444	587.00			28/06/2021	-587.00
36	25 Jun 2021	SUPP LT7328X		6,924.00		25/06/2021	6,924.00
37	25 Jun 2021	SUPP LTA928R		15,916.00		25/06/2021	15,916.00
38	25 Jun 2021	IVPT 2106075 Ltd		2,782.00		25/06/2021	2,782.00
39	24 Jun 2021	COLL VmhxRCy LINK	120.00			24/06/2021	-120.00
40	24 Jun 2021	COLL SP FEE X583K CITY CHARGES	127.40			24/06/2021	-127.40
41	24 Jun 2021	COLL SP FEE XE32Z CITY CHARGES	156.80			24/06/2021	-156.80
42	24 Jun 2021	COLL SP FEE XD8745B CITY CHARGES	127.40			24/06/2021	-127.40
43	24 Jun 2021	COLL SP FEE XD54 CITY CHARGES	127.40			24/06/2021	-127.40
44	24 Jun 2021	COLL SP FEE X247S CITY CHARGES	127.40			24/06/2021	-127.40
45	24 Jun 2021	COLL SP FEE XD147 CITY CHARGES	127.40			24/06/2021	-127.40
46	24 Jun 2021	COLL SP FEE 222B CITY CHARGES	127.40			24/06/2021	-127.40
47	24 Jun 2021	COLL SP FEE X27U CITY CHARGES	127.40			24/06/2021	-127.40
48	24 Jun 2021	COLL SP FEE 3835C CITY CHARGES	127.40			24/06/2021	-127.40
49	24 Jun 2021	COLL SP FEE XD62H CITY CHARGES	127.40			24/06/2021	-127.40
50	24 Jun 2021	COLL SP FEE XD6265H CITY CHARGES	127.40			24/06/2021	-127.40
51	22 Jun 2021	PADD IG731264C SUBSCRIPTIONS	4,479.57			22/06/2021	-4,479.57
52	22 Jun 2021	SUPP 21 PAYMENT		43,492.30		22/06/2021	43,492.30
53	21 Jun 2021	INW CLR 469413	7,211.16			21/06/2021	-7,211.16
Sheet1

Cell Formulas
Range	Formula
F10:F53	F10	=TEXT(LEFT(A10,LEN(A10)-1),"d/mm/yyyy")
G10:G53	G10	=IF(C10<>"","-"&LEFT(C10,LEN(C10)-1),LEFT(D10,LEN(D10)-1))

Desired Result:

Source from Internet.xlsm
	A	B	C	D	E	F	G
1	*Date	*Amount	Payee	Description	Reference	Check Number
2	1/6/2021	-$30,000.00		INW CLR 459223
3	1/6/2021	-$3,226.05		INW CLR 469419
4	1/6/2021	-$585.00		INW CLR 459217
5	1/6/2021	-$235.40		INW CLR 469436
6	1/6/2021	-$233.26		INW CLR 469421
7	1/6/2021	-$173.26		INW CLR 469430
8	1/6/2021	-$133.75		INW CLR 469431
9	1/6/2021	$16,712.77		OTHR S622476923 PAYMENT
10	1/6/2021	$83,476.00		Jobs Support
11	1/6/2021	$57,358.50		CL CHQ DEP
12	1/6/2021	-$960.40		PADD I79646Q FEES
13	1/6/2021	-$24.00		Fund Support
14	1/6/2021	-$9,149.42		CL CHQ DEP
15	1/6/2021	-$1,407.99		INW CLR 458898
16	1/6/2021	$67,733.30		INW CLR 459218
17	2/6/2021	-$4,693.05		INW CLR 469452
18
Desired Result

 

[ec4Excel2]

Just in case you guys wonder why I need the -1 formula, all dates and amount in the downloaded Excel have spaces after the data.