Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bubble Charts with two non-numerical variables | Excel Worksheet Functions | |||
VBA reseting variables | Excel Discussion (Misc queries) | |||
VBA reseting variables | Excel Discussion (Misc queries) | |||
Passing Variables | Excel Discussion (Misc queries) | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions |