Next Line in Auto Filter Macro

[Rich]

Hi

I have a macro which I use so seperate 300 branches data from one master
file into 300 branch files. I have a Macro which copies the filtered sheet,
pastes it onto a new workbook, then saves as "contents of cell b6".xls

I run this macro 300 times from this piece of code:-

Selection.AutoFilter Field:=2, Criteria1:="Branch1"
Application.Run "FERDRUN"

Selection.AutoFilter Field:=2, Criteria1:="Branch2"
Application.Run "FERDRUN"

Selection.AutoFilter Field:=2, Criteria1:="Branch3"
Application.Run "FERDRUN"
etc etc


When new braches are added, I have to edit the code or the new brach will be
missed.

I'd rather just have a single piece of code which steps to the next line in
the auto filter, rather than 300 lines of the above.

I this possible ?

 

[Wigi]

Hi

See for instance http://www.ozgrid.com/forum/showthread.php?t=72736

And also the link in my second post in that topic.

 

[Dave Peterson]

I'd look at these techniques:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

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