Or this way also...

If wsS.Cells(i, 5) Like "*[Cc][Aa]*" Then

--
Rick (MVP - Excel)


"JLGWhiz" wrote in message
...
This will find it if the CA in the search range is upper case. It will
not find it if the CA is lower case. To find it in either case, change
this line:

If wsS.Cells(i, 5) Like "*CA*" Then

To this:

If UCase(wsS.Cells(i, 5)) Like "*CA*" Then

----------------------

Dim wsS As Worksheet, wsD As Worksheet
Dim lr As Long, i As Long, j As Long
Set wsS = Sheets("Sheet1")
Set wsD = Sheets("Sheet2")
lr = wsS.Cells(Rows.Count, "A").End(xlUp).Row
j = 1
For i = 1 To lr
If wsS.Cells(i, 5) Like "*CA*" Then
wsS.Rows(i).Copy wsD.Rows(j)
j = j + 1
End If
Next i





"Souriane" wrote in message
...
Hi all!

The following macro check if there is a "1" in column "E". If so, it
copies the line in sheet2, if not, then nothing.

I would like the same kind of macro but for the chain of characters
"CA" that may appear in the words in the cells.

Can you help me?! Thank you!

Dim wsS As Worksheet, wsD As Worksheet
Dim lr As Long, i As Long, j As Long
Set wsS = Sheets("Sheet1")
Set wsD = Sheets("Sheet2")
lr = wsS.Cells(Rows.Count, "A").End(xlUp).Row
j = 1
For i = 1 To lr
If wsS.Cells(i, 5) = 1 Then
wsS.Rows(i).Copy wsD.Rows(j)
j = j + 1
End If
Next i

Souriane