Filtering Using Variables
 

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


--
Colin Vicary
------------------------------------------------------------------------
Colin Vicary's Profile: http://www.excelforum.com/member.php...o&userid=10472
View this thread: http://www.excelforum.com/showthread...hreadid=545446

Filtering Using Variables
 

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

"Colin Vicary"
wrote in
message news:Colin.Vicary.28d39a_1148557801.0666@excelforu m-nospam.com:

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


--
Colin Vicary
------------------------------------------------------------------------
Colin Vicary's Profile: http://www.excelforum.com/member.php...o&userid=10472
View this thread: http://www.excelforum.com/showthread...hreadid=545446