working with Filter

[diana]

Environment: Office 2007
Excel 2007 - ok to develop and write code in vba. not required to be in vb.net


im a vb Word vba developer, im new to excel development.

this is what Im wanting to achieve:

1. have a master excel spreadsheet
2. Column E has autofilter on.
for for background info and context:
Column Es data content is a mix of numeric and text. the order doesnt matter .e g

84001
84005
60502
87005
IL11CSST
MF11CSST and 87005
74051 & CSST FS01

using code im wanting to
3. for each value
4. set and filter the value
5. the result is displays the filter value
6. obtain the range - data, copy result
7. and paste into new excel spreadsheet (sub spreadsheet)
8. repeat for all values
9. generate a summary status report eg
Completed successfully
or
couldnt write data for filter item "84001" etc


My question is; as im new to excel development whats the best way to work with filters?


I was thinking of the following solution:
1. in Column E perform a search and extract each item
2. place into an array.
3. then for each array item perform the filter, the standard code is

ActiveSheet.Range("$E$1:$E$123").AutoFilter Field:=1, Criteria1:="84015"
ActiveSheet.Range("$E$1:$E$123").AutoFilter Field:=1, Criteria1:="84020"


change to:

ActiveSheet.Range("$E$1:$E$123").AutoFilter Field:=1, Criteria1:=array(currentNum)



This will work however seems alittle long winded to me.

or is there an easier to work with filters?
As excel has already obtained the data in the filter, therefore that should be obtainable using code? (or am i assuming)
using code is there a way I can just obtain all items in the filter and use that?
any examples so i can get started...

many thanks

diana

[sdckapr]

Could you modify the code at:
http://windowssecrets.com/forums/showth ... lter-list-(All" onclick="window.open(this.href);return false;)?p=485044&viewfull=1#post485044

Note: the modification later for the additional rows in later XL versions.

The code prints, but does not create the new sheet (this would be one of the modifications). I would recommend keeping the entire list, not separating into different sheets as it allows one to use filtering and pivots to summarize the data. Filtering on the particular item essentially gives you the "separate sheet" if you want to display or work with it...

As excel has already obtained the data in the filter, therefore that should be obtainable using code?

Excel gets this list at "runtime" for the filter list, does not seem to store it and thus it is unavailable to VB. The code uses a Pivot table to have XL/VB create the list of unique items.

Steve

[diana]

thank you Steve

ill use the link as a code base, and modify according to my requirements.

ill let you know how i go

many thanks again Steve

diana