With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i,"A").Value = "CCC" Then
iNextRow = iNextRow + 1
.Rows(i).Copy Worksheets("Sheet2").Cells(INextrRow,"A")
End If
Next i
End With

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Robert" wrote in message
ups.com...
Hi all,

Can someone advice me which VBA statement to use for substracting a
range of lines that match with for example the value in column A, and
subsequently copy them (whole line) to an empty sheet (say "Sheet2")


Lookup value in column A: CCC

A B C
1 AAA 123 111
2 CCC 345 222
3 BBB 678 333
4 CCC 912 444

Result: line 2 & 4


Any suggestions is mostly appreciated.

Thanks a lot!

Rgds,
Robert