code to loop through column

Here is some code that copies as you specified to column A on sheet2. The original data is in column A of sheet1.

Option Explicit

Sub suit()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim lr As Long, lr2 As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    
    Application.ScreenUpdating = False
    For i = 1 To lr
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
    If s1.Range("A" & i) = "Suit" Then
        s1.Range("A" & i + 1).Copy s2.Range("A" & lr2)
    End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "completed"

End Sub

how would I be able to loop and copy cell that fall between the word "SUIT"

I don't understand what this means.

The code I provided loops through all cells in column A until the last row. If it finds the word "Suit" it will copy the next cell below that and paste it to the next available cell in column A on sheet2. Is this what you asked for in your original thread or did I misunderstand?

Yes, just change the word Suit in the code or build an input box asking for which word to look for and place that variable in the code.

How is this request different from the one you posted here: http://www.excelforum.com/excel-prog...ml#post4229746 where Alf supplied you with an answer which you did not acknowledge. This appears to be a duplicate request which under the forum rules is not allowed.

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.