i have a workbook with over 40,000 records. i need to separate the original into 50 separate workbooks by state. HELP...
i have a workbook with over 40,000 records. i need to separate the original into 50 separate workbooks by state. HELP...
Upload a small sample of your workbook to show how the data is orgazied.
Alf
Here you go. Thank you for being willing to help. Totally lost and on a very tight deadline.
Ok here is a macro that should do what you wish. I've also added some comments.
The sheet where the macro reads the data must be called "Sheet1". Macro uses autofilter to split information and makes a temporary storage to a sheet. This sheet is then saved as a workbook in a folder on the C drive so this folder "All States" on the C drive must exist or the macro will stop.
You can of course change folder name and drive to whatever you like (change string "C:\All States" to suit your needs) and you can also change sheet name "Sheet1" to whatever you wish as long as you replace all instances of "Sheet1" in the macro.
The result will be saved as a workbook named "XX.xls" where XX is the two letter code for the state name and the temporary sheet deleted.
AlfPlease Login or Register to view this content.
Last edited by Alf; 02-01-2013 at 02:23 AM.
Ok. So, I have my sheet named properly and I have the "All States" Folder on the C drive. How do I actually get it to work? Do I record a new Macro and just paste the code into a workbook in A1?
No you need to enter the code in a macro window.Do I record a new Macro and just paste the code into a workbook in A1?
Click on “Developers” tab (Macro1.png)
Then top, left click on “Visual Basic” (Macro1.png)
In the new windows that opens click “Insert” and then click “Module” (Macro2.png)
Copy the code I wrote and paste it in the “Module” window. (Macro3.png)
Close window (click white x in red square)
Click “Macro” to bring up macro window and click “Run” (Macro4.pgn)
Alf
I got all the way to the "Insert Module" part previously, but "Module" was grayed out...
Let me try again. And thank you for the screen captures.
Oh my goodness. I am so appreciative I can't express it!
Thank you so very much.
Welcome to the wonderful world of Excel macros!Oh my goodness. I am so appreciative I can't express it!
I assume from this that it worked the way it was supposed to do. Fine, could you then please mark you post "Solved" as per forum rules and if you like to give my answer a rating click on the small star (bottom left) in my post and give my answer a rating.
Alf
Ps I'm a bit curious as I've never had masses of data like that could you do a guessimate and tell me how long it took from when you clicked "Run" until the job was done?
Ps Ps If you do a rerun you better empty the target folder first as Excel will be nagging you about "Target file exists, do you want to over write?" even if I set
Application.DisplayAlerts = False for the macro run.
Would it also be a Macro that would allow you to take the individual Excel files (by state) and convert them to a specific Avery mailing label?
Alf,
It was so lightening fast--maybe 10 seconds. And I actually had to run multiple files. I renamed the first folder and created a new one with the name you provided. And yes, I will follow the protocol. Thank you for making me aware of it.
Perri
Yes that would certainly be possible I think. But you must supply more information. I would recommend starting a new thread with title "Converting Excel files to Avery mailing label" par example.Would it also be a Macro that would allow you to take the individual Excel files (by state) and convert them to a specific Avery mailing label?
If you look at the macro before the Next statement there is a part where the workbook is created, saved and closed. The next line deletes the worksheet that has all the information for a particular state. If it's easier to mail a worksheet you just put an apostrophe in front of the line
then you keep all the 52 worksheets.Please Login or Register to view this content.
You would also need a bit of code in the beginning to delete all extra sheets unless you prefer to do it manually but that assumes you do this quite often and I got the impression this was a one time job.
Alf
I will. Thank you. I read that in the rules as well. Again, I am so appreciative of your assistance.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks