Hi

I have two sheets in my workbook that I need to search down through an
pull data from to generate a third sheet, depending on whether the las
cell in each row has a Yes or No.

I had set this up manually with 'if' statements, worked fine when I ha
to only search 1 sheet, but now there's 2!

Any ideas

--
Darren
-----------------------------------------------------------------------
DarrenG's Profile: http://www.excelforum.com/member.php...fo&userid=3652
View this thread: http://www.excelforum.com/showthread.php?threadid=56282

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

I'm sure this would be quite easy to do. However, without a specific
example it's difficult to know exactly what your requirements are. Can you
post an example workbook?

"DarrenG" wrote in
message ...

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

I've put together a sample of the Spreadsheet.

You'll see 2 different problem areas with different data, feeding int
a solution area.

Based on whether a column is Yes or No, I would like this to happe
automatically whenever the Workbook is open, and take the data fro
certain cells.

Thanks

- Darren

+-------------------------------------------------------------------
|Filename: Example.zip
|Download: http://www.excelforum.com/attachment.php?postid=5058
+-------------------------------------------------------------------

--
Darren
-----------------------------------------------------------------------
DarrenG's Profile: http://www.excelforum.com/member.php...fo&userid=3652
View this thread: http://www.excelforum.com/showthread.php?threadid=56282

Here is the actual spreadsheet I'm working on.

Anyone help? :confused

+-------------------------------------------------------------------
|Filename: Ex.zip
|Download: http://www.excelforum.com/attachment.php?postid=5059
+-------------------------------------------------------------------

--
Darren
-----------------------------------------------------------------------
DarrenG's Profile: http://www.excelforum.com/member.php...fo&userid=3652
View this thread: http://www.excelforum.com/showthread.php?threadid=56282