excel - Looping through and Copying/Pasting rows VBA

I am looking to write a simple macro in xl VBA which loops through cells A1:A100 and;

1. Finds each cell with the value "HT"


2. Copies that entire row


3. Selects sheets "Records" - travels to the bottom of column A - pastes copied row


4. Loops

Here is my attempt - I'm struggling to get this to work and have been googling all sorts of different methods but can't seem to work it!

Private Sub CopyRow()

Dim i As Range
Sheets("Input").Select
Range("A1").Select

For Each i In Sheet9.Range("A2:A1000")
    Select Case i.Value

        Case "HT"
            Rows(ActiveCell.Row).Copy
            Sheets("Records").Select
            Range("A2").End(xlDown).PasteSpecial xlPasteValues
            Sheets("Input").Select

            Case Else

        End Select


    Next i

Appreciate any help you can give me!

Answer

Your issue is in that “ActiveCell.Row”, where the active cell is not, as I suppose you were assuming, the loop current cell, which is “i”, so you could use “i.Row” instead

But you may also avoid all that selecting/activating and use fully qualified range references:

Private Sub CopyRow()


    Dim i As Range

    With Sheets("Records") ‘reference target sheet
        For Each i In Sheet9.Range("A2:A1000") ‘loop through Sheet9 "A2:A1000" cells
            If i.Value2 = "HT" Then
                i.EntireRow.Copy
                .Cells(.Rows.Count,"A").End(xlUp).Offset(1,0).PasteSpecial xlPasteValues ‘ all “dots” are making following members/object referencing the object referenced in “With...” 
            End If
        Next

    End With
End Sub