VBA Find value, copy and paste values to set range

[drewhx15]

Hello VBA Masters,

Really hoping you can help me, I have been trying to get my code below to work and managed to get it working slightly.

I am trying to have the code check and find "North" in sheet1 column A.
Then it copies the row from columns B:E.
It then pastes the values of all rows with "North" to Sheet2 in a specified cell range e.g., "D5"

I hope this makes sense, if not please let me know.

The below code is moving it, but pushes it down to the bottom or half way down the page in column "A" rather than in where I need it "D5"

Sub MoveData()
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(x1Up).Row

For i = 2 To a

    If Worksheets("Sheet1").Cells(i, 3).Value = "North" Then

    Worksheets("Sheet1").Rows(i).Copy
    Worksheets("Sheet2").Activate
    b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(x1Up).Row
    Worksheets("Sheet2").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Sheet1").Activate
        
    End If

Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

End Sub

 

[GWteB]

See if this works for you ...

Public Sub drewhx15()
    Dim Rng As Range
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set Rng = .Range("A1:E" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    With Rng
        .AutoFilter Field:=1, Criteria1:="=*North*"
        .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1).Copy ThisWorkbook.Worksheets("Sheet2").Range("D5")
        .Parent.AutoFilterMode = False
    End With
End Sub

 

[drewhx15]

GWteB, that has worked perfectly! Thank you so much! Hours, I tried to get that working. Thank you again and have a great evening.

 

[GWteB]

You are welcome and thanks for letting me know