extracting data

[Guest]

I have found several postings similar to this but none that gave me my
answer. I have a worksheet containing several thousand rows. I want to copy
all those rows from worksheet 1 to worksheet 2 based on a yes/no criteria in
column 1. I have tried advanced filter but it appears that this must be used
within the same worksheet. (Not what I want) I also want that new worksheet
to update as the criteria changes from yes to no or vice versa and as new
rows are added to the first worksheet.

Any suggestions?

 

[PY & Associates]

We suggest you look into worksheet change event macro please

 

[Dave Peterson]

You can extract data to another sheet using the techniques shown at Debra
Dalgleish's site:
http://contextures.com/xladvfilter01.html#ExtractWs

Personally, I wouldn't make it as automatic as you suggest. Making a typing
mistake (and doing the copy) could make it very difficult to maintain your
data's integrity.

I'd try to keep the data on one sheet and use data|Filter|autofilter to show
just the rows I wanted to see. By having one location for the data, I won't
have multiple versions of the truth.

But if you must separate your data, you could use that same autofilter to show
just the rows you want and then copy those visible cells to where you want them.

If you want a macro, you could record one when you do it manually.

You may want to try Ron de Bruin's Easyfilter:
http://www.rondebruin.nl/easyfilter.htm

Or look at the way Debra Dalgleish does it:

Debra's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb