I posted this to the wrong group yesterday. My apologies, I'm new to this.

I'm trying to write a macro that will bring up a text box to ask the user
what he/she is looking for (always text). The macro then looks through a
monster sheet of 7000 rows and copies every row that contains the text
string in column D, then deposits the rows into a new sheet. The
text string being searched for is a short piece within a longer string (ie.
'review' within 'project review').

This is what I have so far...I have not been able to copy/paste the row when
I get a hit.

Private Sub Copy_Paste_Rows_w_Match()
Dim ws As Worksheet
Dim targetws As Worksheet
Dim cl As Range, ctextalues As String, tRow As Long
Dim myvalue As String
Dim myrow As Range

If ActiveWorkbook Is Nothing Then Exit Sub

On Error Resume Next
If targetws Is Nothing Then
Set ws = ActiveSheet
Set SourceWB = ActiveWorkbook
Set targetws = Worksheets.Add.Worksheets(1)
Set targetws = ActiveSheet
SourceWB.Activate
ws.Activate
Set SourceWB = Nothing
End If

myvalue = InputBox("Find what?")

Set ws = ActiveSheet
For Each cl In ws.Range("D6:D7000").SpecialCells(xlConstants,
xlTextValues).Cells
ctextvalues = cl
If Len(ctextvalues) 0 Then

If InStr(cl, myvalue) 1 Then ctextvalues =
myrow.targetws.Activate.Cells.Range("A1") = myrow.ws.Activate
' This is where I am stuck. I have not been able to
copy/paste the row when I get a hit.
End If

Set cl = Nothing
End Sub