Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Move rows to sheet based if row contains certain string value

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Move rows to sheet based if row contains certain string value

    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:

    Date Description Amount Balance
    20190201 CREDIT INTEREST 145.6 70124.2
    20190201 TRANSACTION CHARGE SEE CHARGE STATEMENT DETAIL -250 69874.2

    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!

  2. #2
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Move rows to sheet based if row contains certain string value

    Possible with a For...Next loop

    Code:
    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.
    Last edited by Tim_Excel_; Mar 7th, 2019 at 06:15 AM.
    Always make a backup of your data before trying out any code

    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?

    Use [CODE] tags when pasting code in your posts

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move rows to sheet based if row contains certain string value

    Quote Originally Posted by Tim_Excel_ View Post
    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!

  4. #4
    New Member
    Join Date
    Mar 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move rows to sheet based if row contains certain string value


  5. #5
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Move rows to sheet based if row contains certain string value

    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?

    Code:
    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.
    Always make a backup of your data before trying out any code

    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?

    Use [CODE] tags when pasting code in your posts

  6. #6
    New Member
    Join Date
    Mar 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move rows to sheet based if row contains certain string value

    Quote Originally Posted by Tim_Excel_ View Post
    Code:
    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!

  7. #7
    New Member
    Join Date
    Mar 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move rows to sheet based if row contains certain string value

    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?

  8. #8
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Move rows to sheet based if row contains certain string value

    Code:
     
    Dim AddedInterest as Boolean
    Dim AddedTransaction as Boolean
    'Dim other variables
    
    
    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.
    Always make a backup of your data before trying out any code

    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?

    Use [CODE] tags when pasting code in your posts

  9. #9
    New Member
    Join Date
    Mar 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move rows to sheet based if row contains certain string value

    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!

  10. #10
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Move rows to sheet based if row contains certain string value

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

    1) https://stackoverflow.com/questions/...-on-cell-value
    2 & 3) https://www.mrexcel.com/forum/excel-...using-vba.html

    Both links are the first results from a very simply Google query..
    Always make a backup of your data before trying out any code

    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?

    Use [CODE] tags when pasting code in your posts

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •