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 ?

--
Rich
http://www.richdavies.com/excel.htm
http://www.richdavies.com/fantasy-football.htm


** Posted from http://www.teranews.com **

Hi

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

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


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Rich" wrote:

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 ?

--
Rich
http://www.richdavies.com/excel.htm
http://www.richdavies.com/fantasy-football.htm


** Posted from http://www.teranews.com **

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

Rich wrote:

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 ?

--
Rich
http://www.richdavies.com/excel.htm
http://www.richdavies.com/fantasy-football.htm

** Posted from http://www.teranews.com **

--

Dave Peterson