Extract specific data into its own workbook via macro?

Adrian B · 02-21-2005, 03:06 AM

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!

**HiArt** · 02-21-2005, 04:17 AM

If I understand you problem correctly, the answer is quite straight-forward.

decalre a variable and store the name (using your example). Decalre this variable globally if it is going to be used in several subroutines.

Now, when reading you data source, you can compare the name on the row you are processing and compare that to the stored name, extract the date if the same, or if different, process the change and store the new name in the variable.

But...

I also suspect you want to ignore names you have already processed. I would go one of 2 ways here.

First, you could decalre your variable as an array (dim astrnames(100) as String). You could decalre a pinter (Dim intPointer as Integer) to the name you are currently processing. When the name changes, a quick loop around the array to check that trhe name hasn't alreay been encountered. For example:

booFound = False
For intCount = 0 to intPointer-1
    If activecell.value = astrNames(intCount) then
        booFound = True
        ...
    End if
Next intCount

OR

Still use the array idea above. When a new name is encountered in the array use the workbooks.add to create a new workbook, and add the name to the array. When an existing name is found in the array, use the value of intCount to determine which workbook in the collection is relevant. For example:

Workbooks(inCount).Sheets("Sheet1").Activate

This second methoid has the advantage that you can process each name in the raw data as you encounter it, you don't need multiple passes through the data, and this will decarease the overall time taken to process the data.

Hope this makes sence, and helps!

Art

Dave Peterson · 02-21-2005, 07:06 PM

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

Adrian B · 02-24-2005, 03:06 AM

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
>