Excel: How to copy a row if it contains certain text to another worksheet (VBA)

Question

I'm looking to use a marco that would be able to search a column in said sheet and if certain text is found - in my case the word "FAIL" - copy that entire rows data/formatting and paste it into another sheet - sheet 4 in my case - along with any other rows that contained that specific text.

i have been using this code but it only copy pastes one row then stops rather than going through and copying any rows with "FAIL"

Sub Test()
For Each Cell In Sheets(1).Range("H:H")
  If Cell.Value = "FAIL" Then
    matchRow = Cell.Row
    Rows(matchRow & ":" & matchRow).Select
    Rows(matchRow & ":" & matchRow).Select
    Selection.Copy

    Sheets(4).Select
    ActiveSheet.Rows(matchRow).Select
    ActiveSheet.Paste
    Sheets(4).Select
   End If
Next 
End Sub

First post and brand new to VBA so apologies if too vague.

Going through https://stackoverflow.com/q/10714251/11683 will solve your problem. — GSerg, Jul 20 '17 at 08:32
More links for you: [Never Assume The Worksheet](https://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/9218/never-assume-the-worksheet#t=201707200839270059847). The problem is that after first `FAIL` you are **taking** data from `Sheets(4)`. You could find that bug by debugging your code: put text cursor within your code and press F8 to run it line by line. Changes will be visible in your worksheet immediately. — Egan Wolf, Jul 20 '17 at 08:40

score 3 · Accepted Answer · answered Jul 20 '17 at 08:46

Try the code below (explanation inside the code as comments):

Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(1)
    ' loop column H untill last cell with value (not entire column)
    For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
        If Cell.Value = "FAIL" Then
             ' Copy>>Paste in 1-line (no need to use Select)
            .Rows(Cell.Row).Copy Destination:=Sheets(4).Rows(Cell.Row)
        End If
    Next Cell
End With

End Sub

The code runs fast, however it keeps the space between the line. Meaning if there is a row is going to be jumped the new table has empty rows that has been jumped — Nena, Apr 07 '22 at 18:02

score 0 · Answer 2 · edited Sep 20 '17 at 22:21

Try like this:

Option Explicit

Sub TestMe()

    Dim Cell As Range
    Dim matchRow As Long

    With Worksheets(1)    
        For Each Cell In .Range("H:H")
            If Cell.Value = "FAIL" Then
                matchRow = .Cell.Row
                .Rows(matchRow & ":" & matchRow).Select
                .Rows(matchRow & ":" & matchRow).Select
                Selection.Copy        
                Worksheets(4).Select
                Worksheets(4).Rows(matchRow).Select
                Worksheets(4).Paste
                .Select
            End If
        Next        
    End With
End Sub

The problem in your code is that you do not reference the worksheets all the time correctly. Thus it does not work correctly.

As a 2. step, you can try to avoid all the selections in your code, it is a best practice to avoid using either Select or Activate in Excel VBA.

I receive an error "object doesn't support this property or method" for If .Cell.Value = "FAIL" Then — Hufflegigans, Jul 20 '17 at 08:38
That's because of the naming you are using (and thus, I am using as well). Do not name your variables `Cell`, because it may get confused with the Object `Cell`, because of the same name. Now it is fixed - I removed the point before it. — Vityata, Jul 20 '17 at 08:39

Excel: How to copy a row if it contains certain text to another worksheet (VBA)

2 Answers2

Linked