If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |
lookup data in a different workbook | dlaery | General Discussion | 1 | August 13th, 2004 06:15 PM |
Excel Worksheets | Richard | General Discussion | 2 | July 8th, 2004 04:34 AM |
Generate new document with data | Jon Barchenger[MS] | Worksheet Functions | 0 | November 18th, 2003 03:30 PM |
Extract data from a list within a specific date range | Jennifer Bremer | Worksheet Functions | 4 | November 8th, 2003 10:29 PM |