Split Data

[Airgaf]

Hi everyone!

I've give myself a project to do. Normally I used a program at work to do the data downloads individually, export each one to excel, and then save them one at a time and name them by their "Order Number." The order number is unique per data set and is a 9-digit number that always starts with a 3.

Today, instead of doing about 80 separate downloads, I tried to group them together into one large download. Success! Now I have one massive file (11,000+ rows of data)(changes month to month. Can be anywhere from 8,000 to 14,000) with no spaces in between the order numbers.

I was hoping to create a macro that could split my single .xlsx file into as many workbooks as there are order numbers (around 80) and save them (name them) as their order number. Also, I would like the first row to be the first row in every new workbook make (field names)

So what do you guys think, is this possible? (Attached is a sample with all the relevant work info taken out)

[Marcol]

Try this

Please Login or Register  to view this content.

Hope this helps

[EDIT]
Somehow, I don't know why, I thought you needed the file split to worksheets, I hope this still might be of some use perhaps at a later date.

[EDIT]
Add this to the module

Please Login or Register  to view this content.

This now gives you the options
1/. Split data to separate sheets in same workbook
2/. Split sheets to new workbooks (any file you choose)
3/. Do it all in one op. Macro "SplitAndSave"

Hope this helps

[Leith Ross]

Hello Airgaf,

This macro creates individual workbooks for each order number. Each workbook contains the header row of the downloaded workbook and all the data for that order number.

You will need to change the file path in the macro to match where your files will be saved. The path is marked in bold. The macro below has been added to the attached workbook.

Please Login or Register  to view this content.

[Airgaf]

Thank you Marcol and Leith Ross. I ended up using Leith Ross's code. It works great, but I had a few questions about the code. Does setting the DataRang to "A3" make it skip row 2 when copying data to the new workbook? (Probably not, but I'm just curious)

Also, the macro works great when attached to specific workbooks, but doesn't function at all when attached to my Personal Workbook. Can you explain this to me? I'm fine attaching the macro each month to the new data dump since it would only take a few seconds, but once again, I was just curious.

Thank you both for your expertise and hard work. I made sure to add to both of your reputations.

[Airgaf]

Also, how do I change

Please Login or Register  to view this content.

to save the workbooks as .xls files instead of .xlsx?

[Leith Ross]

Hello Airgaf,

I modified the macro code to run in your Personal.xls. The qualifier ThisWorkbook refers to the workbook that contains the macro. When run from "Personal.xls", this is the workbook the macro is referring to which is not the workbook with the data.

The macro now assumes that whichever workbook is the "ActiveWorkbook" will be the workbook with the data. Now, when run from your "Personal.xls" it will refer to the currently active workbook in Excel and not the add-in "Personal.xls". This version will work in both instances:regular workbook macro and in Personal.xls.

Modified Macro Code

Please Login or Register  to view this content.

[Airgaf]

I'm getting an error message, but then again, I messed with the code a bit to try and save the files as .xls files. Here is the code:

Please Login or Register  to view this content.

Here's the error: Run-time error '438': Object doesn't support this property or method

When I click Debug, it highlights

Please Login or Register  to view this content.