excel - How to copy a row if it contains certain text to the next available line in another worksheet

Question

I'm looking to modify the code below that I received from this forum so that it copies to the next available row on Sheet 5.

Private Sub CommandButton1_Click()
    Dim Cell As Range

    With Sheets(1)
        ' loop column H untill last cell with value (not entire column)
        For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
            If Cell.Value = "YES" Then
                 ' Copy>>Paste in 1-line (no need to use Select)
                .Rows(Cell.Row).Copy Destination:=Sheets(5).Rows(Cell.Row)
            End If
        Next Cell
    End With
End Sub

This following code worked for the copy over to the next available row, however If I kept clicking the commandbutton it would keep pasting the information over and over again. It also did not keep the source formatting and formulas, only copied the text. I don't want it to duplicate in the destination sheet and I would like it to copy over the formatting and formulas

Private Sub CommandButton1_Click()
    Dim Cell As Range

    With Sheets(1)
    ' loop column H untill last cell with value (not entire column)
    For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
        If Cell.Value = "Matt Stephens" Then
            .Range("A" & Cell.Row & ":Q" & Cell.Row).Copy
             ' Paste as Links requires to select a destination cell
            Sheets(5).Range("A" & Sheets(5).Cells(Sheets(5).Rows.Count, "A").End(xlUp).Row + 1).Select
            ActiveSheet.Paste Link:=True
        End If
    Next Cell
    End With
End Sub

Answer 1

Try the below:

Private Sub CommandButton1_Click()
Dim Cell As Range
Dim LastRow As Long

Application.ScreenUpdating = False

With Sheets(1)
    ' loop column H untill last cell with value (not entire column)
    For Each Cell In .Range("H1:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)
        If Cell.Value = "YES" And Sheets(1).Range("R" & Cell.Row) <> "Copied" Then
            ' add "Copied" flag so row is not duplicated again
            Sheets(1).Range("R" & Cell.Row).Value = "Copied"
            ' Copy>>Paste in 1-line (no need to use Select)
            LastRow = Sheets(5).Cells(Sheets(5).Rows.Count, "A").End(xlUp).Row + 1
            .Range("A" & Cell.Row & ":Q" & Cell.Row).Copy
            Sheets(5).Rows(LastRow).PasteSpecial xlValues
            Sheets(5).Rows(LastRow).PasteSpecial xlFormats
        End If
    Next Cell
End With

Application.ScreenUpdating = True
Sheet1.Select

End Sub

You need to use PasteSpecial to copy formats, as well as use the LastRow variable to paste into the next available cell and not overwrite previously copied data.

Also, unless you have a unique identifier to check if a cell already exists on the copied sheet, you would need to use a simple flag, as I have done in the above to mark the cell as copied. I am marking the word "Copied" in each row that has been copied after your data in column R. You can move that to any other column or hide it if you don't want it to be visible.