Extracting the desired transactions from Bank Statement

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
I am trying to extract the Dividend received details from the Bank statement for an year to the new sheet. The narration for these transactions starts like “By: …..(Company Name)-……… (Some Ref No)”. The company name and ref no will vary. Only the constant for this transaction is “By:” The following was the recorded macro as an sample to complete the above task. I am not expert at vba.

After extracting the filtered rows to the new sheet, auto sum for the field:=7 shall be done.

Can anyone help me to accomplish the above requirement.



The following are the headers of the Bank statement.

Date
Value Date
Chq No
Narration
Cod
Debit
Credit
Balance


Sub Macro1()

' Macro1 Macro

' Keyboard Shortcut: Ctrl+Shift+B

Selection.AutoFilter

ActiveSheet.Range("$A$1:$H$122").AutoFilter Field := 4, Criteria1 := Array( _

"By: TANNI IND", _

"By: BRIA IND", _

"By: BN IND-", "By: SE LTD-", _

"By: CFI HOES-DIV20-", _

"By: CSIL LTD-","By: CSIL LTD-", _

"By: CRIL LTD-", _

"By: RED LAB-", _

End Sub



Thanking you in Advance



Always Wear Mask. Stay Safe. Work Safe.

buvanamali
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
Hi

Not like that. The Bank Statement will contain several transactions in a year. Out of that transactions, I would like to filter the transactions starting with "By:" followed by company name and reference no. etc. Only above said those rows should be copied and pasted to new sheet. I hope now I am clear in my requirement.

Thanks in advance.

buvanamali
Well the previous code does exactly that. 👇👇👇👇
338c2a1a00456b642d7796058b23fd17d4582d5e.gif


If, for some reason, it does not, I would suggest providing part of your worksheet in question using the xl2bb addin or any free file sharing service like dropbox/onedrive.
That way it will be much easier to work with data in question and suggest solution accordingly.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
Hi M/s fadee2
I googled and found a solution. The following is the code which is working as desired.

Sub FilterRows()
Worksheets("Sheet1").Range("A2").AutoFilter Field:=4, Criteria1:="By:*"
Call CopyFilteredRows
End Sub

Sub CopyFilteredRows()
Dim rng As Range
Dim ws As Worksheet
If Worksheets("Sheet1").AutoFilterMode = False Then
MsgBox "There are no filtered rows"
Exit Sub
End If
Set rng = Worksheets("Sheet1").AutoFilter.Range
Set ws = Worksheets.Add
rng.Copy Range("A1")
End Sub

Can you please help me in combining the above 2 macros into a single one. Also the "Criterial:=" input shall be filled in by a input box.

However thank you very much for your efforts and time spared.

Thanks in advance.

buvanamali
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
hi,
sorry for late reply, was busy in a project.
try the following code

VBA Code:
Sub filter_copy()

Dim rng As Range, ws As Worksheet

Worksheets("Sheet1").Range("A2").AutoFilter Field:=4, Criteria1:="By:*"

If Worksheets("Sheet1").AutoFilterMode = False Then
    MsgBox "There are no filtered rows"
Exit Sub

Set rng = Worksheets("Sheet1").AutoFilter.Range
Set ws = Worksheets.Add

rng.Copy Range("A1")

End Sub

above code is a combination of both of your procedures, nothing omitted.

hth...
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
Hi M/s fadee2

Thank you very much. It is working fine. One more help Sir.

I would like to fine tune the above macro so that any body having no knowledge about macro should use it and derive the benefit out of it.
This macro should be placed in a excel file. When you open this excel file and press the button, it should be prompted for a source file, if source file is selected, it should do the rest of the job and paste the filtered data to a new workbook with input file name to save it.

Sorry for troubling you.

Thanks in advance.

buvanamali
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,776
Members
415,927
Latest member
vedasinternational

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