You may want to run a macro whenever you need the extraction.
Debra Dalgleish's has some code that does this kind of thing:
http://www.contextures.com/excelfiles.html
There are a couple of files you may want to steal from:
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
Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb
Mah wrote:
Thank you Dave & John for your suggestions on using Filter.
However what I'm looking for is something that could create the subset
list automatically based on an Item value (say 20). Then I could just
change the Item value to get any subset list.
Dave Peterson wrote in message ...
I'd just select the range and do Data|filter|Autofilter
then show the items you want
(and copy them to a new sheet if really necessary)
--
Dave Peterson