I have written the code below that currently returns a message box value of “yes” for each row the word “Transfer” appears in column “G” in the “Logistics worksheet. Instead of a message box to appear, I would like to copy the entire row and paste it to the next available row in the worksheet “Transfers”. I have done most of these functions on their own (copy a row, paste to new worksheet, search a range), but for wahtever reason I cant figure out how to combine them together.

Sub Transfers()

Dim Myrange As Range

Set Myrange = Worksheets(“Logistics”).Range(“G2:G500”)

For Each Cell In Myrange
If InStr(LCase(Cell.Value), LCase(“TRANSFER”)) <> 0 Then
MsgBox “yes”

End If
Next Cell

End Sub

I have tried the below code but get an “Application-defined or object-definced error”

Sub Transfers

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastRow As Long, j As Long
Set Myrange = Worksheets(“Logistics”).Range(“G2:G500”)

Set ws1 = ThisWorkbook.Sheets("Logistics")
Set ws2 = ThisWorkbook.Sheets("Transfers")
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For Each Cell In Myrange
    If InStr(LCase(Cell.Value), LCase("TRANSFER")) <> 0 Then
        lastRow = lastRow + 1
        ws1.Rows(j).EntireRow.Copy ws2.Range("A" & lastRow)
    End If
Next Cell

End Sub