VBL copy paste to new worksheet

jlrich12002

New Member
Joined
Jun 9, 2018
Messages
4
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
How about
Code:
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top