Find on Sheet1, copy to Sheet2, Find Next Loop

Hi, I need to find any cells that contain "A" in Column E on Sheet1, copy cells A1, C1, D1, from that row and paste them to Sheet2, starting at A3.

Sheet1.Select
    ActiveCell.Offset(0, -3).Range("A1,C1,D1").Select
    ActiveCell.Activate
    Selection.Copy
    Sheet2.Select
    A3.Select
    Selection.Paste
    Application.CutCopyMode = False

I'm not sure how best to loop through Sheet1 and also paste in the next row down on sheet 2.

I could also copy everything from Sheet1 A-E to Sheet2 and then delete rows that don't have A in column E. This might be easier. It may vary how many rows have data in them - checking on column B, C or D (not A)

Any guidance will be much appreciated.

Thanks.

Hi ker9,

See if this code makes sense

Sub CopySht1ToSht2()
    Dim RowCtr1 As Double
    Dim RowCtr2 As Double
    Dim WS2 As Worksheet
    
    Set WS2 = Worksheets("Sheet2")
    RowCtr2 = 3
    For RowCtr1 = 1 To Cells(Rows.Count, "E").End(xlUp).Row
        If InStr(Cells(RowCtr1, "E"), "A") > 0 Then
            With WS2
                .Cells(RowCtr2, "A") = Cells(RowCtr1, "A")
                .Cells(RowCtr2, "B") = Cells(RowCtr1, "C")
                .Cells(RowCtr2, "C") = Cells(RowCtr1, "D")
                RowCtr2 = RowCtr2 + 1
            End With
        End If
    Next RowCtr1
End Sub

Watch out for the PERIODS in front of "Cell". They mean using the With WS2. The Cell without the period in front means the active worksheet.