VBL copy paste to new worksheet

[jlrich12002]

I want to copy and paste a row to a new sheet when a cell in that row contains a certain text string (not the whole cell value as shown in code below). How do I modify this code so that it finds any row that contains the string? Also, if you could edit this so that it never appends the rows to the bottom of the new sheet – I always want it to rewrite over the entire sheet if the macro is run.

Thank you so much for any help that you can provide.



Private Sub CommandButton1_Click()
a = Worksheets("PnP Inventory List").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a


If Worksheets("PnP Inventory List").Cells(i, 5).value = ("insert text string here") Then
Worksheets("PnP Inventory List").Rows(i).Copy
Worksheets("ECCJROnly").Activate
b = Worksheets("ECCJROnly").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("ECCJROnly").Cells(b + 1, 1).Select
ActiveSheet.Paste

End If


Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("PnP Inventory List").Cells(1, 1).Select



End Sub

 

[Fluff]

How about

Sub jlrich12002()
   Sheets("ECCJROnly").UsedRange.Offset(1).ClearContents
   With Sheets("PnP Inventory List")
      .Range("A4:E4").AutoFilter 5, "*something*"
      .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("ECCJROnly").Range("A2")
      .AutoFilterMode = False
   End With
End Sub

 

[jlrich12002]

THANK YOU Fluff!!! So clean, and worked perfect!!

 

[Fluff]

You're welcome & thanks for the feedback