Extract specific data into its own workbook via macro?

I have data in a workbook that I need to extract into several workbooks.
For simplicity sake: data = "Sam, Paul, Ann" etc. How can I extract the
data that pertains to Sam, Paul, Ann, etc. into thier own workbook
automatically via a macro? I have been able to get to the point where I
have created a macro that extracts the info for "Sam", and copies it into a
new workbook. But I can only do this one at a time. I have to edit the
criteria in the macro for each extraction I want to to. In other words,
after extracting "Sam's" data, I edit the macro's criteria to "Paul", save
the macro and run it again. Then I get a new workbook with Paul's data.
This takes a very long time for over 100 different criterias! Is there a way
I can create the macro to extract the data for each change in criteria? In
other words, each time the name changes from Sam to Paula, to Ann. Extract
all the data for each, and copy it into its own workbook. This would save me
tons of time!

Someone please help. I know there is a way to do it but I am not a VB person.

Thanks!

Maybe you could steal some 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

Adrian B wrote:

I have data in a workbook that I need to extract into several workbooks.
For simplicity sake: data = "Sam, Paul, Ann" etc. How can I extract the
data that pertains to Sam, Paul, Ann, etc. into thier own workbook
automatically via a macro? I have been able to get to the point where I
have created a macro that extracts the info for "Sam", and copies it into a
new workbook. But I can only do this one at a time. I have to edit the
criteria in the macro for each extraction I want to to. In other words,
after extracting "Sam's" data, I edit the macro's criteria to "Paul", save
the macro and run it again. Then I get a new workbook with Paul's data.
This takes a very long time for over 100 different criterias! Is there a way
I can create the macro to extract the data for each change in criteria? In
other words, each time the name changes from Sam to Paula, to Ann. Extract
all the data for each, and copy it into its own workbook. This would save me
tons of time!

Someone please help. I know there is a way to do it but I am not a VB person.

Thanks!

--

Dave Peterson

Thanks Dave! For the quick reply and also for the link to Debra's Site. I
was able to use some of her code and incorporate it into my own macro. With
a few revisions I was able to do exactly what I wanted.

Debra's site is great.

Thanks again!!!


"Dave Peterson" wrote:

Maybe you could steal some 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

Adrian B wrote:

I have data in a workbook that I need to extract into several workbooks.
For simplicity sake: data = "Sam, Paul, Ann" etc. How can I extract the
data that pertains to Sam, Paul, Ann, etc. into thier own workbook
automatically via a macro? I have been able to get to the point where I
have created a macro that extracts the info for "Sam", and copies it into a
new workbook. But I can only do this one at a time. I have to edit the
criteria in the macro for each extraction I want to to. In other words,
after extracting "Sam's" data, I edit the macro's criteria to "Paul", save
the macro and run it again. Then I get a new workbook with Paul's data.
This takes a very long time for over 100 different criterias! Is there a way
I can create the macro to extract the data for each change in criteria? In
other words, each time the name changes from Sam to Paula, to Ann. Extract
all the data for each, and copy it into its own workbook. This would save me
tons of time!

Someone please help. I know there is a way to do it but I am not a VB person.

Thanks!

--

Dave Peterson