How can I sort rows into different worksheets?

[nithchun]

Hello, I would appreciate any input on an excel solution I'm trying to create.

I'm working on a project where every week, I pull data from a system that tracks the status of overdue invoices. Each invoice has its own row and my job is to take all of the invoices and sort them into three different spreadsheets based on which business function needs to take action to get them paid. The first business function is Vendor Management. The way that I would identify if an invoice and all it's information would go into the Vendor Management tab is:

1. Each invoice row has 10 fields describing different things about the invoice
2. If the "comments" field has the phrase "DO NOT APPROVE" somewhere within its contents (all of them are strings), the invoice would be sorted into the Vendor Management tab
3. I would need to find a way to automate copy pasting the whole row into the Vendor Management tab by manually finding "DO NOT APPROVE" in the string of the comments field

Current ideas: Do some sort of SEARCH function or HLOOKUP function to find rows with "DO NOT APPROVE" in the comments field. However I'm struggling to find a function that will also copy and paste the whole row after identifying "DO NOT APPROVE" in any of the comments fields.

Please let me know if you have any ideas or need more information.
Thanks for the help!

 

[Logit]

Here is one solution :

Option Explicit

Sub CpyPste()
Dim cell As Range

    For Each cell In Range("A2:A100")  'change range here to match your needs
        If InStr(cell.Value, "Do Not Approve") > 0 Then
            cell.EntireRow.Copy

'chenge sheet name to match your needs
            Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
                PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=True
                Application.CutCopyMode = False
        End If
    Next cell

    Sheets("Sheet2").Select  'chenge sheet name to match your needs
    Range("A1").Select
End Sub

 

[nithchun]

Here is one solution :

Option Explicit

Sub CpyPste()
Dim cell As Range

    For Each cell In Range("A2:A100")  'change range here to match your needs
        If InStr(cell.Value, "Do Not Approve") > 0 Then
            cell.EntireRow.Copy

'chenge sheet name to match your needs
            Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
                PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=True
                Application.CutCopyMode = False
        End If
    Next cell

    Sheets("Sheet2").Select  'chenge sheet name to match your needs
    Range("A1").Select
End Sub
[/CO
[/QUOTE]
I don't have much experience with VBA so I'm struggling to understand why this won't work for me. For some reason, when I run the macro, no rows with "**DO NOT APPROVE**" appear. Here is a screen shot of my code and workbook for reference.