Slightly different way:
Sub newone()
Dim RngColE As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngColE = Range("E1", Range("E" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set Dest = .Range("A1")
End With
For Each i In RngColE
If i.Value = "CA" Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub


HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Joel" wrote:

from
If wsS.Cells(i, 5) = 1 Then

to
If Instr(wsS.Cells(i, 5), "CA") 0 Then


"Souriane" wrote:

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