Need Excel macro that will look at text in specific field and copy to a corresponding sheet based on the text found

[stressler]

I am looking for a macro that will read column D on my "Data Sheet" tab and depending on the text found copy and paste the entire row to the corresponding sheet in the workbook. As and example, if the phrase Not Covered is found in column D on Data Sheet, copy the entire row to the worksheet called "Not Covered". I'll need this to repeat for all rows on "Data Sheet" so I could end up with many rows of copied data on the "Not Covered" worksheet. I'll have the following phrases in column D on "Data Sheet" and corresponding worksheets, Not Covered, No Run, Not Completed, Blocked and Failed. I'll need "Data Sheet" reviewed for all of these phrases and the entire row copied and pasted into the corresponding worksheet with same name for all rows of data on "Data Sheet". Then I have two more specific requests for two additional tabs. Not Testable and Passed. Not Testable criteria is that in the text to be found in column D on data sheet is "N/A" and column E on "Data Sheet" is not empty. Then for Passed worksheet, the criteria is that column D on data sheet has the word Passed in it or has N/A in column D of data sheet and column E of data sheet is empty. So the last two phrases where I want to copy the full row from Data Sheet to the corresponding worksheet (Not Testable and Passed) have multiple criteria to be searched. Any help would be appreciated. I can create a macro on each individual sheet and run it so that it pulls from the Data Sheet and pastes into each individual worksheet, but that's a lot of clicking of the Run Macro button unnecessarily. I need a macro that will complete all searches in one run of the macro from the "Data Sheet" tab.

 

[MARK858]

Just a slightly more efficient version of the previous code (still untested) and still assumes that you have headers on all columns between A & E on Data Sheet

Sub stressler2()
    Dim arr, i As Long
    arr = Array("Not Covered", "No Run", "Not Completed", "Blocked", "Failed")
    Application.ScreenUpdating = False
    
    With Sheets("Data Sheet").Range("A1:E" & Sheets("Data Sheet").Columns("A:E").Find("*", , xlValues, , xlByRows, xlPrevious).Row)
        
        For i = LBound(arr) To UBound(arr)
            .AutoFilter Field:=4, Criteria1:=arr(i)
            On Error Resume Next
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
            Sheets(arr(i)).Range("A" & Rows.Count).End(xlUp).Offset(1)
            On Error GoTo 0
            Sheets("Data Sheet").ShowAllData
        Next
        
        .AutoFilter 4, "#N/A"
        .AutoFilter 5, "<>"
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
        Sheets("Not Testable").Range("A" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0
        Sheets("Data Sheet").ShowAllData
        
        .AutoFilter 4, "#N/A"
        .AutoFilter 5, "="
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
        Sheets("Passed").Range("A" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0
        Sheets("Data Sheet").ShowAllData
        
        .AutoFilter 4, "Passed"
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
        Sheets("Passed").Range("A" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0
        .AutoFilter
        
        Application.ScreenUpdating = True
        
    End With

End Sub

 

[My Aswer Is This]

Now that Mark is back. I will move on to another posting. I'm sure Mark will fill all your needs.

 

[stressler]

Thank you so much Mark858! I used your first macro and it's working beautifully, I had to remove the # before N/A, but it works great. I'm going to switch to your more efficient one tomorrow though to see if it's working even faster or if it's working exactly the same, I'll use the more efficient one as a go forward. Thank you very much for your help on this. And thank you My Answer Is This as well for the start, it was really nice to see it all coming together and working just as I need!!

 

[MARK858]

to see if it's working even faster or if it's working exactly the same

The 2nd one is faster it just depends on how much data you have whether you notice it

 

[My Aswer Is This]

Thank you so much Mark858! I used your first macro and it's working beautifully, I had to remove the # before N/A, but it works great. I'm going to switch to your more efficient one tomorrow though to see if it's working even faster or if it's working exactly the same, I'll use the more efficient one as a go forward. Thank you very much for your help on this. And thank you My Answer Is This as well for the start, it was really nice to see it all coming together and working just as I need!!

Glad we were able to help you.

Come back here to Mr. Excel next time you need additional assistance.