Sub ABC()
Dim sh3 as Worksheet, sh as Worksheet
Dim rng as Range, cell as Range, cell1 as Range
Dim i as Long, rw as Long
Set sh3 = Worksheets(3)
rw = 2
for i = 1 to 2
set sh = worksheets(i)
set rng = sh.range(sh.cells(2,1),sh.cells(rows.count,1))
for each cell in rng
set cell1 = sh.Cells(cell.row,"IV").End(xltoLeft)
if lcase(cell1.Value) = "yes" then
cell.EntireRow.copy sh3.Cells(rw,1)
rw = rw + 1
end if
next
Next
end Sub

--
Regards,
Tom Ogilvy

"DarrenG" wrote:

>
> Hi
>
> I have two sheets in my workbook that I need to search down through and
> pull data from to generate a third sheet, depending on whether the last
> cell in each row has a Yes or No.
>
> I had set this up manually with 'if' statements, worked fine when I had
> to only search 1 sheet, but now there's 2!
>
> Any ideas?
>
>
> --
> DarrenG
> ------------------------------------------------------------------------
> DarrenG's Profile: http://www.excelforum.com/member.php...o&userid=36529
> View this thread: http://www.excelforum.com/showthread...hreadid=562825
>
>