Extracting the desired transactions from Bank Statement

[buvanamali]

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]

Assuming your "copy from" sheet is sheet1 and your "copy to" sheet is sheet2, try the following codes in a standard module.

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

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]

Assuming your "copy from" sheet is sheet1 and your "copy to" sheet is sheet2, try the following codes in a standard module.

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

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]

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]

which line??

 

[buvanamali]

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

 

[fadee2]

what is the name of your data sheet??

 

[buvanamali]

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]

you only need to copy narration column and nothing else??

 

[buvanamali]

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