Automatic Sheet Generation???

[DarrenG]

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?

[Tom Ogilvy]

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
>
>

[Graham Whitehead]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[DarrenG]

I've put together a sample of the Spreadsheet.

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

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

Thanks

- DarrenG

[DarrenG]

Here is the actual spreadsheet I'm working on.

Anyone help?