How to split Excel worksheet into multiple files?

[VBA_Newbie]

Hi,

How do I split an Excel worksheet with 10,000+ rows into multiple Excel files based on the values in column D via a Macro/VBA? The file has 9 columns (A:I), I also need the split files to automatically save in a specified folder.

Thanks!

microsoft-excel vba macros

[#2]

@VBA_Newbie 

I'm not qualified to directly answer  your question regarding VBA and its use in dividing your worksheet up.

I am qualified to ask you "Why?" And I do that not just to be a pain in the neck; rather, although I fully recognize there may be fully justifiable reasons, I also am a defender of the power of Excel to work with large databases, using such things as dates--the basis for your desired splitting--as the basis for looking at and analyzing effectively subsets within that whole. Breaking it apart, on the other hand, makes it more difficult to do overall or all-encompassing analysis, which you also might want to do.

So you begin your posting with this statement, "I need to split a worksheet with 10,000 rows into multiple Excel files based on the dates in column D," and I just am asking whether you really NEED to do that. You give no reason. As I said, there may be a perfectly valid reason. But in general--for others who come here to the techcommunity site with workbooks consisting of monthly or yearly sheets--you'll see many a recommendation to first consolidate those separated sheets into one so as to be able to accomplish the all-encompassing analysis they're trying to accomplish.

[#3]

@VBA_Newbie 

Can you upload a sample file to work with and another file which shows the desired output you are trying to achieve?

Do you want to run the macro from Excel itself or from another application like Access?

[#4]

@mathetes Not sure about the poster, but I've had to do this so I can create individual spreadsheets for multiple individuals.

For example I have a mass spreadsheet of associates that need to have some kind of change recorded, but the associates are managed by all different people, so I want to send each supervisor, which would be a column on the spreadsheet, their own spreadsheet with only the associates they are responsible for.

This may be to keep things simple or due to the fact that the data may contain some sensitive items that we don't want everyone to see.

365 seems to be reacting a bit different from the old VB scripts I had.  For example 1 script that used to split the sheet into multiple tabs/worksheets, creates the proper tabs, but no longer seems to move the actual data over.  Usually I'd move the master to worksheets, validate and then move the sheets to their own files. 

[#5]

I have 15,000 lines of data I need to split into 150 worksheets, sorted by Name in Column D. Is there a way to do this in Excel and not requiring additional software/add-in?