To pull all the rows from Sheet1 to Sheet2 depending on specific Word via VBA-CodePudding

I have 2 work sheets.

Sheet1 contains lots of rows and columns, whereas in sheet2 i would like to copy all the rows (Complete row) from sheet1 depending on a specific word (e.g. "South" which is mentioned in Sheet1 column A), this means that whenever the code finds "South" it should copy the whole row from Sheet1 to Sheet2.

i have found a code in which i am stuck.

Sub fhskdfh()

Sheets("Sheet1").Select

Range("D1").Select

Dim mycode As Worksheet

Set mycode = ThisWorkbook.Worksheets("Sheet2")

Dim i As Long

For i = 2 To Cells(Rows.Count, "D").End(xlUp).Row

   f Cells(i, 4).Value = "South" Then

        Range(Cells(i, 1), Cells(i, 8)).Copy Destination:=mycode.Range("A" &

mycode.Cells(Rows.Count, "A").End(xlUp).Row 1)

    End If

Next i

End Sub

Please note that in sheet1 we will continue to add the data on daily basis but the aim is it should copy all the data (even new data added) based on the criteria mentioned above.

Also it does not require any msg box.

Can anyone out of you can help please

Thanks

This is still not copying the Code, Do i need to change the Range("D1").Select to Range("A1").Select because the word "South" lookup value is in Sheet1 and column A. Also how about For i = 2 To Cells(Rows.Count, "D").End(xlUp).Row to For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row

i will appreciate if you could please guide regarding this

CodePudding user response：

To solve this problem, there is no need for a loop, it is enough to filter the data and copy it all at once

Sub GetSouth()
    With ActiveWorkbook.Sheets("Sheet1")
        .Range("A1").AutoFilter Field:=1, Criteria1:="South" ' filter data
        .Range("A1").CurrentRegion.Copy .Parent.Sheets("Sheet2").Range("A1") ' copy filtered data
        .AutoFilterMode = False ' remove filter
    End With
End Sub