Was this not close to what you wanted?
Simon Lloyd;269845 Wrote:
This should do what you need:

Code:
--------------------
Dim rng As Range
Dim MyCell As Range
Set rng = Sheets("Sheet1").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each MyCell In rng
If LCase(MyCell.Offset(0, 1).Value) = LCase("Yes") Then
Range(Cells(MyCell.Row, 1), Cells(MyCell.Row, 2)).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next MyCell
--------------------



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=75202