Move rows to sheet based if row contains certain string value

[aaelex]

Hi all

Wondering if you could help. I have seen other older threads here but couldn't get them to work for me.

I have a bank statement I am importing via CSV into Excel.

Example data (Sheet 1) is in the following format:

[TABLE="width: 783"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Description[/TD]
[TD]Amount[/TD]
[TD]Balance
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 587"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:2346;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:17109;width:401pt" width="535"> <col style="mso-width-source:userset;mso-width-alt:2730;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:2858;width:67pt" width="89"> </colgroup><tbody>[TR]
[TD="width: 73, align: right"]20190201[/TD]
[TD="width: 535"]CREDIT INTEREST
[/TD]
[TD="width: 85, align: right"]145.6
[/TD]
[TD="width: 89, align: right"]70124.2
[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> [TABLE="width: 587"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:2346;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:17109;width:401pt" width="535"> <col style="mso-width-source:userset;mso-width-alt:2730;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:2858;width:67pt" width="89"> </colgroup><tbody>[TR]
[TD="width: 73, align: right"]20190201[/TD]
[TD="width: 535"]TRANSACTION CHARGE SEE CHARGE STATEMENT DETAIL
[/TD]
[TD="width: 85, align: right"]-250[/TD]
[TD="width: 89, align: right"]69874.2
[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>
I want to move/organise the data into seperate sheets based on the description value above.
E.g If cell contains "INTEREST" move to a sheet called INTEREST.
E.g. If cell contacts TRANSACTION CHARGE move to a sheet called TRANSACTION CHARGE etc.

I want it to delete the source row after it has run. So move and not copy.

There are quite a few checks I want to do on the statement and sort into respective sheets (INTEREST, TRANSACTION CHARGE ...etc)

Thank you so much for your time in advance!

 

[Tim_Excel_]

Possible with a For...Next loop

With Workbooks(REF).Sheets(REF)

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LRow
    c = .Cells(i, "B").Value     
    If c Like "*INTEREST*" Then
        DestLRow = Workbooks(REF).Sheets("INTEREST").Cells(Workbooks(REF).Sheets("INTEREST").Rows.Count, "A").End(xlUp).Row + 1
        Workbooks(REF).Sheets("INTEREST").Rows(DestLRow).Value = .Rows(i).Value
        .Rows(i).EntireRow.Delete
        i = i -1
        LRow = LRow - 1
    ElseIf c Like "*TRANSACTION CHARGE*" Then
        DestLRow = Workbooks(REF).Sheets("TRANSACTION CHARGE").Cells(Workbooks(REF).Sheets("TRANSACTION CHARGE").Rows.Count, "A").End(xlUp).Row + 1
        Workbooks(REF).Sheets("TRANSACTION CHARGE").Rows(DestLRow).Value = .Rows(i).Value
        .Rows(i).EntireRow.Delete
        i = i -1
        LRow = LRow - 1
    ElseIf '...and so on
         'etc.
    End If
Next i

End With

Alternatively you could use Find() to loop through the sheet quicker, but I reckon you want to go through all cells.

 

[aaelex]

Possible with a For...Next loop

Alternatively you could use Find() to loop through the sheet quicker, but I reckon you want to go through all cells.

Thank you for this!

I created a sub and pasted the code however I an error "Sub-script out of range".

Please excuse my lack of experience!

 

[aaelex]

https://pasteboard.co/I4jhDZ9.png

 

[Tim_Excel_]

Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

With Workbooks(REF).Sheets(REF)

Above code, and other instances, is supposed to be edited to match your workspace.

Give this article about object referencing a read, as well as this lesson about using With..End With.

 

[aaelex]

With Workbooks(REF).Sheets(REF)

Above code, and other instances, is supposed to be edited to match your workspace.

Give this article about object referencing a read, as well as this lesson about using With..End With.

Got it and it works great thank you!

 

[aaelex]

Would it be simple enough to add a line of code to create the sheets (INTEREST, TRANSACTION CHARGE) etc in the macro if those strings are found rather than assume they are pre-existing?

 

[Tim_Excel_]

Dim AddedInterest as Boolean
Dim Added[COLOR=#333333]Transaction as Boolean
'Dim other variables


[/COLOR]If c Like "*INTEREST*" Then
        If AddedInterest = False Then
            Workbooks(REF).Sheets.Add(after:=Workbooks(REF).Sheets(Workbooks(REF).Sheets.Count)).Name = "INTEREST"
            AddedInterest = True
        End If
        DestLRow = Workbooks(REF).Sheets("INTEREST").Cells(Workbooks(REF).Sheets("INTEREST").Rows.Count, "A").End(xlUp).Row + 1
        Workbooks(REF).Sheets("INTEREST").Rows(DestLRow).Value = .Rows(i).Value
        .Rows(i).EntireRow.Delete
        i = i -1 
        LRow = LRow - 1
ElseIf...

The first time you encounter a certain string, it adds a sheet and sets a variable to True. The second time it encounters this string, it won't add another sheet because the variable is no longer set to False.

 

[aaelex]

Thanks for this I modified it to my needs and it works great!

Could you help me with the line of code to :

1) Colour a row based on a certain search value e.g. "If c Like "*INTEREST*" Then"
2) Make a copy of the original sheet with a certain name
2) Rename the original sheet to a certain name

Many thanks!

 

[Tim_Excel_]

Try to look for answers online before asking questions on forums, especially on threads which differ from your question.

1) https://stackoverflow.com/questions/25000691/vba-color-entire-row-based-on-cell-value
2 & 3) https://www.mrexcel.com/forum/excel-questions/494503-copy-worksheet-rename-using-vba.html

Both links are the first results from a very simply Google query..