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

ec4Excel2

New Member
Joined
Dec 6, 2019
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1
2
3
4
5
6
7
8
9DateTransaction DescriptionWithdrawalsDepositsDate1 Column
1030 Jun 2021 INW CLR 459223 30,000.00 30/06/2021-30,000.00
1130 Jun 2021 INW CLR 469419 3,226.05 30/06/2021-3,226.05
1230 Jun 2021 INW CLR 459217 585.00 30/06/2021-585.00
1330 Jun 2021 INW CLR 469436 235.40 30/06/2021-235.40
1430 Jun 2021 INW CLR 469421 233.26 30/06/2021-233.26
1530 Jun 2021 INW CLR 469430 173.26 30/06/2021-173.26
1630 Jun 2021 INW CLR 469431 133.75 30/06/2021-133.75
1730 Jun 2021 COLL wgcWNCTUn0l 252.00 30/06/2021-252.00
1830 Jun 2021 PADD 1254 CUSTOMS 2,997.54 30/06/2021-2,997.54
1929 Jun 2021 OTHR S622476923 PAYMENT16,712.77 29/06/202116,712.77
2029 Jun 2021 GOVT Jobs Support Scheme IRAS 83,476.00 29/06/202183,476.00
2129 Jun 2021 CL CHQ DEP 57,358.50 29/06/202157,358.50
2229 Jun 2021 COLL 1154776 CO LTD6,853.84 29/06/2021-6,853.84
2329 Jun 2021 PADD 785380I FUNDS220.39 29/06/2021-220.39
2429 Jun 2021 PADD IG469Q CHARGES873.45 29/06/2021-873.45
2529 Jun 2021 COLL S6222708SRP ELECTRONICS54.27 29/06/2021-54.27
2629 Jun 2021 PADD I79646Q FEES873.45 29/06/2021-873.45
2729 Jun 2021 PADD I79646Q FEES16,712.77 29/06/202116,712.77
2829 Jun 2021 Fund Support83,476.00 29/06/202183,476.00
2929 Jun 2021 CL CHQ DEP 57,358.50 29/06/202157,358.50
3028 Jun 2021 INW CLR 458898 6,907.92 28/06/2021-6,907.92
3128 Jun 2021 INW CLR 459218 3,959.00 28/06/2021-3,959.00
3228 Jun 2021 INW CLR 469452 2,800.00 28/06/2021-2,800.00
3328 Jun 2021 INW CLR 469451 1,900.00 28/06/2021-1,900.00
3428 Jun 2021 INW CLR 469441 682.00 28/06/2021-682.00
3528 Jun 2021 INW CLR 469444 587.00 28/06/2021-587.00
3625 Jun 2021 SUPP LT7328X 6,924.00 25/06/20216,924.00
3725 Jun 2021 SUPP LTA928R15,916.00 25/06/202115,916.00
3825 Jun 2021 IVPT 2106075 Ltd 2,782.00 25/06/20212,782.00
3924 Jun 2021 COLL VmhxRCy LINK120.00 24/06/2021-120.00
4024 Jun 2021 COLL SP FEE X583K CITY CHARGES127.40 24/06/2021-127.40
4124 Jun 2021 COLL SP FEE XE32Z CITY CHARGES156.80 24/06/2021-156.80
4224 Jun 2021 COLL SP FEE XD8745B CITY CHARGES127.40 24/06/2021-127.40
4324 Jun 2021 COLL SP FEE XD54 CITY CHARGES127.40 24/06/2021-127.40
4424 Jun 2021 COLL SP FEE X247S CITY CHARGES127.40 24/06/2021-127.40
4524 Jun 2021 COLL SP FEE XD147 CITY CHARGES127.40 24/06/2021-127.40
4624 Jun 2021 COLL SP FEE 222B CITY CHARGES127.40 24/06/2021-127.40
4724 Jun 2021 COLL SP FEE X27U CITY CHARGES127.40 24/06/2021-127.40
4824 Jun 2021 COLL SP FEE 3835C CITY CHARGES127.40 24/06/2021-127.40
4924 Jun 2021 COLL SP FEE XD62H CITY CHARGES127.40 24/06/2021-127.40
5024 Jun 2021 COLL SP FEE XD6265H CITY CHARGES127.40 24/06/2021-127.40
5122 Jun 2021 PADD IG731264C SUBSCRIPTIONS4,479.57 22/06/2021-4,479.57
5222 Jun 2021 SUPP 21 PAYMENT43,492.30 22/06/202143,492.30
5321 Jun 2021 INW CLR 469413 7,211.16 21/06/2021-7,211.16
Sheet1
Cell Formulas
RangeFormula
F10:F53F10=TEXT(LEFT(A10,LEN(A10)-1),"d/mm/yyyy")
G10:G53G10=IF(C10<>"","-"&LEFT(C10,LEN(C10)-1),LEFT(D10,LEN(D10)-1))


Desired Result:

Source from Internet.xlsm
ABCDEFG
1*Date*AmountPayeeDescriptionReferenceCheck Number
21/6/2021-$30,000.00INW CLR 459223
31/6/2021-$3,226.05INW CLR 469419
41/6/2021-$585.00INW CLR 459217
51/6/2021-$235.40INW CLR 469436
61/6/2021-$233.26INW CLR 469421
71/6/2021-$173.26INW CLR 469430
81/6/2021-$133.75INW CLR 469431
91/6/2021$16,712.77OTHR S622476923 PAYMENT
101/6/2021$83,476.00Jobs Support
111/6/2021$57,358.50CL CHQ DEP
121/6/2021-$960.40PADD I79646Q FEES
131/6/2021-$24.00Fund Support
141/6/2021-$9,149.42CL CHQ DEP
151/6/2021-$1,407.99INW CLR 458898
161/6/2021$67,733.30INW CLR 459218
172/6/2021-$4,693.05INW CLR 469452
18
Desired Result
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

ec4Excel2

New Member
Joined
Dec 6, 2019
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Amount with space.jpg
    Amount with space.jpg
    193.3 KB · Views: 3
  • Date with space.jpg
    Date with space.jpg
    151.4 KB · Views: 3

Forum statistics

Threads
1,141,062
Messages
5,704,061
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top