autofilter into another worksheet

[yolanda.silva]

Since I had such amazing success with my other problem, I'm hoping to
get some help with this problem, too! I'll try to explain as best
as I can... please keep in mind that I don't know VB so if the answer
involves a VB macro - I'll need the steps on how/where to insert it
and what I should change to names of things I call it...

Ok... I have a database that combines the data from many other
databases into one and then removes all empty rows.

The new workbook, at this point, is called Sheet1 - which is fine,
because the name doesn't matter. I can save it afterwards. What I
need is for this data to be sorted by one column and each different
option in that column to be in a separate worksheet.

The first sheet is currently called Sheet1, as well. This is the only
sheet in this workbook, currently, because it's a macro that creates
this workbook.

There are 5 columns (A-E). The row number is not going to be known
until the data runs.

In column D there are 10 possible options that may exist... I want
each of those ten options to be on a separate worksheet that is CALLED
the option.

The ten options are as follows: Accept //// Decline //// Already in
Contract //// Language Barrier //// Suspended/Cancelled //// Seasonal
Standby /// No Authorized Contact //// Wrong Number //// Answering
Machine //// Other (Please Specify)

Is there a way to make the primary page stay, as is, with all data and
then have it filter out the individual results of each option into a
separate worksheet? I'd love ANY help!!

Thanks!!

 

[Dave Peterson]

I'd keep all that data in one worksheet if at all possible. If someone updates
the "child" worksheet and expects you to update the "parent" worksheet, then
you'll find that it turns into a mess.

But if you're generating those worksheets for just reports...

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm