Copying entire row using Instr

Question

Hi im wanting to loop through each row in my spreadsheet and for each instance it finds the word "North East" to copy that row into the North East sheet. This will be repeated on a weekly basis and therefore i need the script to check if the entry already exists within the North East sheet and if it does to do nothing and move onto the next row. I am unsure how to do this as im a total vba novice. Any help would be appreciated.

thanks

Sub Contain_Copy()
Dim ranger As Long
Dim lastrow As Long
Dim FromSheet As Worksheet, ToSheet As Worksheet

Set FromSheet = Sheets("Master")
Set ToSheet = Sheets("NE")
lastrow = FromSheet.Cells(Rows.Count, "G").End(xlUp).Row

For ranger = 2 To lastrow
    If InStr(1, FromSheet.Cells(ranger, "G"), "North East") > 0 Then
        FromSheet.Cells(ranger, "G").EntireRow.Copy _
        Destination:=ToSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
Next ranger
End Sub

Answer 1

Here is one approach (though CLR's is arguably more efficient) using Match to check first whether the column A value already exists in column A of the NE sheet.

Sub Contain_Copy()

Dim ranger As Long
Dim lastrow As Long
Dim FromSheet As Worksheet, ToSheet As Worksheet, v As Variant

Set FromSheet = Sheets("Master")
Set ToSheet = Sheets("NE")
lastrow = FromSheet.Cells(Rows.Count, "G").End(xlUp).Row

For ranger = 2 To lastrow
    If InStr(1, FromSheet.Cells(ranger, "G"), "North East") > 0 Then
        v = Application.Match(FromSheet.Cells(ranger, "A"), ToSheet.Columns(1), 0)
        If IsError(v) Then
            FromSheet.Cells(ranger, "G").EntireRow.Copy _
            Destination:=ToSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    End If
Next ranger

End Sub