How to split an Excel file into multiple files using VBA | Excel Automation

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.

Excel format list

To download the Excel file used in this video & VBA source code

► Buy Me a Coffee? Your support is much appreciated!
-------------------------------------------------------------------------------------
☕ Venmo: @Jie-Jenn

► Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------

#Excel #ExcelAutomation #SplitExcelFile

[sandrac.]

We got a 17 000 lines ticket sales database to split in my company, your tutorial saved us DAYS of tedious work !! I don't know how to thanks you enough !!!

[AnthonyCollins-hy]

Thanks mate, i have 300, 000 rows of data and ended up with 181 files, life saver.

[MK]

You saved me... Before using this macro, I have spent 1 month for split and merge huge excel files. Thank you so much!! Please enjoy coffee from me 😉

[celiomansini]

You have saved my life! Enjoy your coffee. Best regards from Rio de Janeiro!

[edward_lee]

Thank you, sir! This VBA was a life saver for something I needed to get done by today!

[mes]

What would I write so that my output files names are saved like such as
Test_CA_Version1.xlsx
Where "Test" and "Version" are manual inputs into another field on a second worksheet?

[nihadalbibani]

Works great for me, however if I have other tabs in the file can those tabs be copied including the formulas in those tabs?, also can I keep the name of the tab to stay in the new files?

[muhammadfahadazamjamil]

Thank you so much for the detailed video you're the best.

[beitsbe]

Thank you! this was so simple to follow and saved me so much time!

[adhyayana]

hi, I have an excel with 2 sheets, am unable to split them into separate workbooks. If you could help please

[tamaradissanayake]

I am getting an error with this text string. Any advise? I also downloaded your workfiles to test and it was the same.

col.Add .Cells(RowNumber, "A").Value, CStr(.Cells(RowNumber, "A").Value)
Next RowNumber

[leotos]

Hello! What if I have a table? Seems like its not functioning if you have to maintain a table format.. :( otherwise it would be perfect... :(

[albanbytyqi]

Thank you. For some reason the video is very blurred and I cannot make out the content displayed. I wonder if this my problem or it is a problem for other viewers?

[dallasxal]

I tried it and it is creating each separate file but all files contain the all data. It is as if it is copy pastying all data and not only data from Filtered category value

[tejasvirao]

Hello !!! Thank you for this tutorial. It was really helpful.

I was able to split the data into different workbooks but when giving the Saveas command, I do not find the variables Save or Saveas. I only see SavedasArray. Can you help me how to define the variable Save and SaveAs ?

Maybe due this I'm seeing the error Method 'Add' o object 'Workbooks' failed.

Please help

[Ohmwreckerr]

This tutorial is totally relevant to what I'm working right know, I've already tried the code and it's working base on what I've want, however the only error I've encountered is that if the naming of the file is exceeding my 31 characters how can I resolve this error Champ?

[weixue]

Hi, your video is really helpful! one quick question regarding the autofilter, what if I need to set up my filter on the second row of Column A, should I put ".AutoFilter .Range("A2").Column, Category_Name"?

Everything works fine. However, i would want the new workbooks created to have same column width as the source sheet. How do i do that ?

[alicemorili]

Great video. How can I add some text at top of each new worksheet and start the list below that text?

[bimalmehta]

This was helpful. However i have another request and don't know how do it.. I have a file with 10k records and want to split this file with 25 records in each file but keep the headers in for each file