splitting a workshheet into multiple sheets

[James]

Hi everyone,
I have a large spreadsheet that I need to split into multiple workbooks by
the change in data. for example,
in A1-A5 I have "Jones"
in A6 it changes to "Smith". I need a way to grab all the data for Jones and
create a new sheet with his data, then a new sheet for Smith's data. Is this
possible with a macro?
Also, is there a way to also have the new sheets name themselves after cell
data, for example name the "Jones" sheet "Jones" instead of Book 1?

 

[HF]

Hi James

I am also looking for the same solution, posted my , have
you gotten any reply, pls share with me if you have the
solution.

Btw, do you know how to merge multiple worksheets, exact
opposite of what you have mentioned below? Pls share if
you know the solution.

Thanks & regards,
HF

 

[Dave Peterson]

Debra Dalgleish has a couple of workbooks that use advanced filter to do this
kind of thing at:
http://www.contextures.com/excelfiles.html

Look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items,
creates a sheet for each item, then replaces old data with current.
AdvFilterCity.xls 46 kb

and

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

 

[James]

Hey Ron,
Thanks for posting this reply! I've almost figured it out. However, I can't
seem to get the sheets to autoname correctly. any ideas?
ALSO, how would I do this to create new workBOOKS instead of sheets? Is that
possible?