In this tutorial, we are going to write an automation script to split a table in Excel using VBA.
Using VBA, you can break down a source worksheet to multiple Excel file based on the values in the selected key columns, and doing so, will keep your data secure without oversharing unwanted information. Many solutions out there rely on 3rd party addins, require payment to download the software. With VBA, it is free, and you can modify the script to meet your specific need.
Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn
Download the source file → LINK
Download the final file → LINK
Source Code (Updated 12/30/2021):
Hi Jie,
I would like to know how difficult would it be to recreate this same exercise but with additional 7 columns and assign them to the belonging folder (same name, organized with the same hierarchy).
Best,
A lifesaver! Thanks.
Glad my script helped!
That is great and saves so many hours of manual work!
I have two questions:
1. Assuming there are multiple spreadsheets in the file I’m using can the code be modified to save new files based on the column values in one sheet (as it does now) but then also save all other spreadsheets unchanged in new files?
2. How to modify the code to split based on a value in a column but also copy all rows below the table unchanged to new files
Enjoy the coffee 🙂
Hi Agata, can you send me an email to YouTube@LearnDataAnalysis.org with your question along with sample file illustrate the problem?
This I Great. How do i adjust to save it as CSV File?
I followed the tutorial and he enters in “.xlsx” in a section so maybe ctrl + f and replace as a different file type?
Hi!
I copied the script and ran it but a runtime error “1004: Clearcontents method of Range class failed. Why is that so and how do I solve it?
I copied the script into my file, did not download the source file.
“ClearContent”. Second C is capitalized.
I seem to be able to run the code, and the helper file lists my expected categories. However, no files are generated in my output folder. Any suggestions? I am trying to write to a network drive, although I don’t know why that would be an issue. Thanks in advance.
Is your file stored in One Drive?
Hello Jie Jenn, I am having this same problem. My files are not stored in One Drive
This is excellent and really clear for a beginner such as myself.
Quick question, IF we wanted to retain the column length of the worksheet for the created worksheets, what would I need to add to the Macro?
Thanks
Can you be more specific?
Hi Jie,
This looks great. I’ve tried running but the output files are containing all of the source data rather than the filtered data by the category generated in the helper sheet. Any ideas what I’m doing wrong?
Thanks in advance.
Had the same issue, i removed all formating from the sheet and set output to a local drive instead of onedrive, worked fine after that.. hope it helps!
Hi Jie,
This code is great help, will u able to advice me how can I get the painter format paste over to new worksheet as well?
My second question, if I have a workbook with 3 worksheets. Each worksheet has a branch column.
I need to break the workbook to multiple workbooks (each workbook with 3 worksheets) on specific branch.
Really frustrating- I watched the tutorial and wrote the VBA code, checked for errors and all good, but it won’t create the individual files 😦 I’ve checked the references and can’t see any issues.
Have over 330 separate files to create so this would have been a great time saver but will have to sort them out manually now.
Hi Jie,
thank you first for the vedio and the code. I’ve run it and it generates the files but the output files are empty . Any ideas why?
Thanks dear
hello, i had problem using this VBA,
runtime error 1004
wbTarget.SaveAs Target_Folder & Category_Name & “.xlsx”, 51
I have the same case with you, but I can not resloved it…;(
I removed Target_Folder and it cleared this error
To automatically create the link in Excel to the path just save the master file to the same file you want to save the new Workbooks in
Hey Great Stuff,
just a quick query what if i want to add one more category along with existing, like say taking your example, presently i am only taking “state” as a category but what if i want to filter out “state” along with “City” as my final output….
I have used only a few lines of your code in my project and successfully working. Thanks for posting this valuable code.
How do I change the source sheet to be my active sheet? Everything works great, but want to be able to use code on any file I am working on.
This is awesome! This has helped me a lot, but I would need if possible a little more help.
I would like to separate a Dataset using your code, but the output files I would like to name them always the same, independently of the values of the column.
For example: WB1.xlsx, WB2.xlsx, WB3.xlsx, etc. instead of using the unique values of the column (in your example, the states names like AZ.xlsx, CA.xlsx,…) as the name of each file.
How could I edit your Source code to do this? Thanks in advance for your help!
I am getting a debug message with the following text string. Please could you advise? Also tested your files on my system and got the same message.
col.Add .Cells(RowNumber, “A”).Value, CStr(.Cells(RowNumber, “A”).Value)
Next RowNumber
This was very helpful and I am grateful for you sharing your knowledge with us. Thank you so much. I did have one question how would I be able to include multiple worksheets in the saving process? For example, some columns have a data validation list attached (dropdown list) to them in a separate worksheet and I wanted to save both worksheets.
Thank you so much for sharing your knowledge. It is so helpful.
I would like to ask you, how can I split the data into separate sheet in the
Hi Jie,
this tutorial and VBA code is life changing. Thank you very much for that! However I would desperately need one more modification, which I have no clue how to do it. Would you be so kind to provide me guidance on how to change code if you have “header” that consists of 10 rows. My data starts at row 11, however, in each file I would like to keep all the header data from row 1-10. Thank you!
I have this working but i also want to exclude anything in another colum that contains ‘0’.
For example QTY. So the end result give me only rows with a 1 or above in column ‘QTY’
Hi! Can I verify how can I change the file name in VBA related to the codes you provided?
Nice video , i have one Large CSV file holding 12 Million rows i want to split this CSV into multiple file each file will be 1 million record
I ran the VBA Code it created separate files, but with all the data in all of the folders. I am not saving on one drive, but it is a network file. Also if it matters I have my data in Power query.
in ‘Private Sub SplitWorksheet(ByVal Category_Name As Variant)’ instead of ‘.copy’ use ‘.SpecialCells(xlCellTypeVisible).Copy’
This saved my team hours of manual work. Came up on a deadline and a fancy tool failed us. Thank you so freakin’ much.
Hi, this is a life saver, it took me ages to save more than 500 files for the suppliers. I have one question, I have an Excel template in which i paste the data and then there is a pivot table which updates automatically. Can there be a second “Helper” sheet with the pivot?
many thanks
it creates the separate file but only with the heading row, no value rows under the heading row.
Any suggestions ?
Hi Jie,
When I run “?collectionUniqueList.Count” in Immediate, I get “0” as a return. If I follow through with the rest of the code, there is no errors in debug, however it doesn’t create files. It only fills in the unique categories in the helper sheet. Any ideas what I did wrong?
Thanks, Chris