How do you split an excel spreadsheet into multiple files based on columns in vba?

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: //www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn

Download the source file → LINK

Download the final file → LINK

Source Code [Updated 12/30/2021]:

At times, in data analysis, you may need to split the contents of an Excel worksheet into multiple Excel workbooks according to a specific column. Now, in this post, we will teach you 2 quick ways to get it.

Many users frequently need to split an Excel worksheet that contains huge rows of data into multiple separate Excel workbooks based on a specific column. For instance, here is my sample Excel worksheet. I would like to split this sheet’s data on basis of the “Price of single license [US$]” column into multiple workbooks.

In general, you’ll tend to use the following Method 1 to manually filter and copy data. But, it will be quite tedious and stupid if there are too many filter options. Therefore, here we also show a much more convenience way – Method 2, which uses VBA. Now, read on to get them in detail.

Method 1: Copy Contents to Separate Excel Workbooks after Filter

1. At first, select a cell in the specific column, like “Cell B1” in my own instance.
2. Then, turn to “Data” tab and click “Filter” button.
3. Next, click the “down arrow” button in the column header to display a list of filter choices.
4. Now, uncheck the “[Select All]” option.
5. After that, you can select one filter choice, like “29.95” in my example, and click “OK”.
6. At once, only the data, whose value in Column B is “29.95”, will be left.
7. Then, copy the filtered data and paste them into a new Excel workbook.
8. Later, use the same way to split the other data to separate Excel workbooks.

Method 2: Batch Split Contents into Multiple Excel Workbooks via VBA

1. In the first place, ensure that the specific worksheet is opened.
2. Next, launch VBA editor according to “How to Run VBA Code in Your Excel“.
3. Then, put the following code into “ThisWorkbook” project.

Sub SplitSheetDataIntoMultipleWorkbooksBasedOnSpecificColumn[]
    Dim objWorksheet As Excel.Worksheet
    Dim nLastRow, nRow, nNextRow As Integer
    Dim strColumnValue As String
    Dim objDictionary As Object
    Dim varColumnValues As Variant
    Dim varColumnValue As Variant
    Dim objExcelWorkbook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
 
    Set objWorksheet = ActiveSheet
    nLastRow = objWorksheet.Range["A" & objWorksheet.Rows.Count].End[xlUp].Row
 
    Set objDictionary = CreateObject["Scripting.Dictionary"]
 
    For nRow = 2 To nLastRow
        'Get the specific Column
        'Here my instance is "B" column
        'You can change it to your case
        strColumnValue = objWorksheet.Range["B" & nRow].Value
 
        If objDictionary.Exists[strColumnValue] = False Then
           objDictionary.Add strColumnValue, 1
        End If
    Next
 
    varColumnValues = objDictionary.Keys
 
    For i = LBound[varColumnValues] To UBound[varColumnValues]
        varColumnValue = varColumnValues[i]
 
        'Create a new Excel workbook
        Set objExcelWorkbook = Excel.Application.Workbooks.Add
        Set objSheet = objExcelWorkbook.Sheets[1]
        objSheet.Name = objWorksheet.Name
 
        objWorksheet.Rows[1].EntireRow.Copy
        objSheet.Activate
        objSheet.Range["A1"].Select
        objSheet.Paste
 
        For nRow = 2 To nLastRow
            If CStr[objWorksheet.Range["B" & nRow].Value] = CStr[varColumnValue] Then
               'Copy data with the same column "B" value to new workbook
               objWorksheet.Rows[nRow].EntireRow.Copy
  
               nNextRow = objSheet.Range["A" & objWorksheet.Rows.Count].End[xlUp].Row + 1
               objSheet.Range["A" & nNextRow].Select
               objSheet.Paste
               objSheet.Columns["A:B"].AutoFit
            End If
        Next
    Next
End Sub

1. After that, click the “Run” icon in the toolbar or press “F5” key button.
2. When macro finishes, the separate Excel workbooks will be created with the split data from the source Excel worksheet.
3. Each workbook will look like the following screenshot.

Comparison

Prevent Excel Data Loss

Though MS Excel is becoming more and more advanced and sophisticated, it still tends to crash from time to time due to miscellaneous factors, such as malicious third party add-ins or human errors and so on. Since Excel crash can directly lead to Excel corruption, so as to avoid Excel data loss, you have to back up your Excel files on a regular basis. Otherwise, you need to apply an Excel repair tool, such as DataNumen Excel Repair to fix corrupted Excel files.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server repair and outlook repair software products. For more information visit www.datanumen.com