Thread: How do I copy a row from one worksheet to another by looking for a character string?

I need to look for a character string in the cells in row A. If the cell contains "GRA" I need to copy the row to the worksheet named "A". If the cell contains "GRB" I need to copy the row to the worksheet named "B" and so on. There can be multiple strings found in one cell. I have all of my worksheets named and I do not have control over how many rows there are in any of the worksheets. I am using Excel 2010.

Because you work in the medical field, I put a little bit more effort haha... The only way I can think of is writing a macro. Take a look the file. Basically, the code is going to look at Column A row #2 to the last row. If the cell contains the key word, then it is going to copy the entire row to the destination worksheet.

Sub Test()
Dim LastRow As Long, i As Long
Dim x As Long, y As Long
Dim cell As Range
Dim WSA As Worksheet, WSB As Worksheet, WS As Worksheet

Set WS = Worksheets("Source")
Set WSA = Worksheets("A")
Set WSB = Worksheets("B")

LastRow = WS.Cells(Rows.Count, 1).End(xlUp).Row
x = 0
y = 0
For i = 2 To LastRow
    If WS.Cells(i, 1).Value Like "*GRA*" Then
        WS.Rows(i).Copy WSA.Rows(x + 1)
        x = x + 1
    ElseIf WS.Cells(i, 1).Value Like "*GRB*" Then
        WS.Rows(i).Copy WSB.Rows(y + 1)
        y = y + 1
    End If
Next i

End Sub

JieJenn, since the column A strings may have multiple matching strings, I don't think you want to use ElseIf, I believe you want to run each test fully, the same row may copy to multiple sheets.

For i = 2 To LastRow
    If WS.Cells(i, 1).Value Like "*GRA*" Then _
        WS.Rows(i).Copy WSA.Range("A" & Rows.Count).End(xlUp).Offset(1)
    If WS.Cells(i, 1).Value Like "*GRB*" Then _
        WS.Rows(i).Copy WSB.Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i

Good call. Thanks.

Per JBeaucaire's suggestion

Sub Test()
Dim LastRow As Long, i As Long
Dim x As Long, y As Long
Dim cell As Range
Dim WSA As Worksheet, WSB As Worksheet, WS As Worksheet

Set WS = Worksheets("Source")
Set WSA = Worksheets("A")
Set WSB = Worksheets("B")

LastRow = WS.Cells(Rows.Count, 1).End(xlUp).Row
x = 0
y = 0
For i = 2 To LastRow
    If WS.Cells(i, 1).Value Like "*GRA*" Then
        WS.Rows(i).Copy WSA.Rows(x + 1)
        x = x + 1
    End If

   If WS.Cells(i, 1).Value Like "*GRB*" Then
        WS.Rows(i).Copy WSB.Rows(y + 1)
        y = y + 1
    End If

Next i

End Sub

I took out the X and Y variables because I imagine the OP is going to add numerous "checks", no need to have a separate "variable" for each sheet individually when an OFFSET() will drop the data into the next empty row.

Thanks for the feedback.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.