Bob replied to the original thread with a correction for the typo INextrRow
to INextRow

dim iLastRow as Long, iNextRow as Long
Dim i as Long
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(INextRow,"A")
End If
Next i
End With

--
Regards,
Tom Ogilvy


"Robert" wrote in message
oups.com...
Hi,

This morning I posted a question how to copy lines with a specific
value to an empty sheet.
I got a promt reaction including the below statement but when running
the macro I am getting an error on .Rows(i).Copy
Worksheets("Sheet2").Cells(INextrRow,"A")
Can someone tell me if this is a result of the fact that VB does not
know where to paste the first line? and if so can you please advice
how to solve this (including some sort of offset function)?

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



Many thanks for your willingness!

Rgds,
Robert