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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
Assuming your "copy from" sheet is sheet1 and your "copy to" sheet is sheet2, try the following codes in a standard module.

VBA Code:
Sub filter_data()
Sheets("sheet1").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-"), Operator:=xlFilterValues

copy_data
End Sub

VBA Code:
Sub copy_data()
Dim rng As Range

lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Sheets("sheet1").Range("a2", "h" & lr)

visiblecellscount = Sheets("sheet1").Range("a2:a" & lr).Cells.SpecialCells(xlCellTypeVisible).Count
If visiblecellscount > 1 Then rng.SpecialCells(xlCellTypeVisible).Copy

Sheets("sheet2").Activate
Sheets("sheet2").Range("a2").PasteSpecial Paste:=xlPasteValues

lrow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("sheet2").Range(Cells(2, 1), Cells(lrow, 1)).NumberFormat = "dd/mm/yy"

End Sub

hth....
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
Assuming your "copy from" sheet is sheet1 and your "copy to" sheet is sheet2, try the following codes in a standard module.

VBA Code:
Sub filter_data()
Sheets("sheet1").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-"), Operator:=xlFilterValues

copy_data
End Sub

VBA Code:
Sub copy_data()
Dim rng As Range

[COLOR=rgb(247, 218, 100)]lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row[/COLOR]
Set rng = Sheets("sheet1").Range("a2", "h" & lr)

visiblecellscount = Sheets("sheet1").Range("a2:a" & lr).Cells.SpecialCells(xlCellTypeVisible).Count
If visiblecellscount > 1 Then rng.SpecialCells(xlCellTypeVisible).Copy

Sheets("sheet2").Activate
Sheets("sheet2").Range("a2").PasteSpecial Paste:=xlPasteValues

lrow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("sheet2").Range(Cells(2, 1), Cells(lrow, 1)).NumberFormat = "dd/mm/yy"

End Sub

hth....
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
Hi
I am getting subscript out of range error code 9 at the line coloured yellow above. Please help me for resolution.

Thanks in advance.

buvanamali
 

fadee2

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

ADVERTISEMENT

which line??
 

fadee2

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

ADVERTISEMENT

what is the name of your data sheet??
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
Hi
which line??
lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

In this line only I am getting the error mentioned in my previous post.
buvanamali
what is the name of your data sheet??
Hi
Sorry, after changing the sheet name as Sheet1, it works perfect, but not as requeseted/required by me. I requested / expected to filter the data starting from row two till end - the narration ie. field:=4 starting as "By:" rest sentence whatever it is, the same has to be filtered and pasted to next sheet. I hope now I am clear in my requirement.

buvanamali
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
you only need to copy narration column and nothing else??
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

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