Filtering Using Variables

[Colin Vicary]

Hello All

Google has failed me on this one, so I hope one of you can help.

I have a file of 40,000 product codes, each product has a supplier
code. I want to produce a new worksheet for each of the 200 suppliers.

I put a list of suppliers in a new worksheet.

I was planning to use AutoFilter to identify all the lines with that
supplier code and then do the new worksheet thing.

What I can't work out what to do is to tell Excel to find the supplier
code in cell A1, do the filtering, then find the supplier code in A2
and so on.

I got this far, but I guess the syntax is wrong..

For Each SupCode In Range("[SupCodeList.xls]Sheet1!R1C1:R200C1")
Columns("E:E").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=SupCode
DO WHAT I NEED TO DO
Next SupCode

Can anyone help please?

Thanks

Colin

 

[JimMay]

This site has a good example:
Scroll down to "FILTERS" - Header
And then Create New Sheets from Filtered List (Number 4);
HTH


http://www.contextures.com/excelfiles.html