Copy & Paste Rows that Contain Specific Text

[fogdogzz]

I have used the following code to copy and paste rows that have values in Column "C" that are greater than the value of cell W62. I like the structure of it because it runs more quickly than some other macro options. However, I am having a difficult time adjusting it to copy rows that contain the word "Sales" in Column "D". [Note the individual cell might say "Sales1" or "SalesBC", not just "Sales"- so the contains aspect is key]. I am hoping that someone could add some insight.

Thank you very much!

Current Macro:

Dim rws1 As Range, cell1 As Range, value As Long

Sheets("Sheet1").Select
Set cell1 = Range("c2")
Do Until cell1.value = ""
value = Val(cell1.value)
If (value < Sheets("Sheet1").Range("w62").value) Then
If rws1 Is Nothing Then
Set rws1 = cell1.EntireRow
Else
Set rws1 = Union(cell1.EntireRow, rws1)
End If
End If
Set cell1 = cell1.Offset(1)
Loop
If Not rws1 Is Nothing Then rws1.Copy
Sheets("Sheet2").Range("A2").PasteSpecial xlPasteValues

Set rws1 = Nothing
Set cell1 = Nothing

End Sub

 

[farmerscott]

Is this a specific code you want to use on Col D only or do you want to loop thru Col C at the same time, using your rules above?

FarmerScott

 

[fogdogzz]

Good question! I should have been more clear. I am looking for separate code- just for Col D (and the contains "Sales" parameters).

Thanks, Scott.

Is this a specific code you want to use on Col D only or do you want to loop thru Col C at the same time, using your rules above?

FarmerScott

 

[farmerscott]

FogDogzz,

try-

Sub find_and_copy()
Dim rws1 As Range, cell1 As Range, value As Long, lastrow As Long

Sheets("Sheet1").Activate
Set cell1 = Sheets("Sheet1").Range("D2")
Set fval = cell1.Find("Sales", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

Do Until cell1.value = ""
If cell1.value = fval Then

lastrow = Sheets("Sheet2").Cells.SpecialCells(xlLastCell).Row + 1
Set rws1 = cell1.EntireRow
rws1.Copy
Sheets("Sheet2").Range("A" & lastrow).PasteSpecial xlPasteValues
End If
Loop
End Sub

Hope that helps,

FarmerScott