separate large file into separate worksheets (by state)

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. 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?

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.


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.

Oh my goodness. I am so appreciative I can't express it!

Welcome to the wonderful world of Excel macros!

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

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?

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.

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

Please Login or Register  to view this content.

then you keep all the 52 worksheets.

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.