How to Split an Excel Sheet into Multiple Worksheets

Data split is a concept widely used in data science for creating sets of data to train machine learning models. At the same time, when we talk about splitting data in a spreadsheet, it usually means data partitioning – dividing a large dataset into smaller subsets. These subsets are extracted based on a shared attribute of a column (vertical split) or row (horizontal split). 

In Google Sheets, the major Excel competitor, such data split can be done manually with the help of functions, such as QUERY, FILTER, and others. However, Excel, despite its supremacy in functionality, does not offer anything like this. So, to split an Excel sheet into multiple worksheets, you’ll either need to do this manually or with the help of VBA Macro Editor. In this tutorial, I’m going to reveal the ins and outs of each way, so buckle up and let’s go! 

What’s the purpose of splitting worksheets?

The main value of splitting datasets is to obtain smaller chunks of data that are easier to manage and analyze. You may not need to split an Excel sheet into multiple worksheets if the row count is up to 50. However, if you deal with a tremendously large dataset that is frequently refreshed, then it makes sense for you to split it into smaller partitions based on a specific attribute or criterion.

If you and your project have a use case related to splitting or partitioning data, share it with us. You can get a free consultancy offered by our data expert service.

As an example for this tutorial, I took a dataset exported from Pipedrive to Excel using Coupler.io, a data automation and analytics platform. The dataset counts 2826 rows, which makes it a perfect candidate for being split into several sheets. The attribute for data split is the status of deals: won, lost, or open.

So, I’m going to split the Excel sheet into multiple workbooks based on a column – in my case, column A. Do not worry, I’ll also show an example on how to split Excel sheet into multiple sheets based on rows. But first things first.

How to split an Excel sheet into multiple worksheets: VBA option

As I said above, you have two options to split an Excel sheet into multiple worksheets: manual and automated. The automated one can be implemented using the VBA macro editor as follows:

• Go to the Developer tab of your Excel workbook and select Visual Basic.

Note: If you don’t see the Developer tab on your Excel menu, you need to enable it. Go to File => Options => Customize Ribbon, and tick the Developer tab.

• In the open window Microsoft Visual Basic for Applications, go to Insert => select Module.

• Here, you need to write a VBA script to split an Excel sheet into multiple worksheets. Save the module for your VBA macro after that.

The next step would be to run the macro: Select Macros (next to the Visual Basic button), then, in the open window, select the needed macro and click Run.

Let’s have a look at a hands-on example of how I split an Excel sheet into multiple sheets based on cell value.

VBA script to split Excel sheet into multiple sheets based on column value

For my example, I created a VBA script, which does the following:

• Identifies unique values from column A
• Creates separate sheets according to the unique values from column A
• Filters the dataset (columns A to F) by unique values from column A
• Copies the filtered records to the newly created sheets

Sub SplitSheetIntoMultipleSheetsBasedOnColumn()
    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 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
        strColumnValue = objWorksheet.Range("A" & 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)
        Set objSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        objSheet.Name = varColumnValue
        objWorksheet.Rows(1).EntireRow.Copy objSheet.Rows(1)
        For nRow = 2 To nLastRow
            If CStr(objWorksheet.Range("A" & nRow).Value) = CStr(varColumnValue) Then
                objWorksheet.Rows(nRow).EntireRow.Copy
                nNextRow = objSheet.Range("A" & objSheet.Rows.Count).End(xlUp).Row + 1
                objSheet.Range("A" & nNextRow).PasteSpecial xlPasteValuesAndNumberFormats
            End If
        Next
        objSheet.Columns("A:F").AutoFit
    Next
End Sub

After I run the macro, it will return the following:

Each sheet will contain rows specific to a certain status of Pipedrive deals: lost, won, and open. 

Note: If you want to apply this script for your needs, make sure to tweak the variables of a column to split by and range of your dataset.

VBA script to split Excel sheet into multiple files based on column value

As an alternative, I decided to tune this script, so it will split an Excel sheet into multiple files, i.e. workbooks. I inserted a new module – go to Visual Basic, right-click on Modules, then select Insert => Module. 

I added the following script and saved it:

Sub SplitSheetIntoMultipleWorkbooksBasedOnColumn()
    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
        strColumnValue = objWorksheet.Range("A" & 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)
        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("A" & nRow).Value) = CStr(varColumnValue) Then
               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:F").AutoFit
            End If
        Next
    Next
End Sub

When I run the macro, it will split the Excel sheet into multiple files. In my case, these are three separate workbooks based on the column value.

How to split Excel sheet into multiple worksheets based on rows

I also promised to demonstrate the horizontal data split, i.e., split Excel sheet into multiple sheets based on rows. This could be useful if you need to partition your dataset into sheets or files with a fixed number of rows. 

Let’s check out how this works in the example of our dataset imported from Pipedrive. However, for this particular case, I reduced the number of rows in the dataset to 10.

Now, I’m going to add a module, which allows me to split the dataset by a specified number of rows. As I explained above,  you need to go to Visual Basic, right-click on Modules, then select Insert => Module. And here is a script that will split the Excel sheet into multiple worksheets.

Sub SplitExcelSheetIntoMultipleSheetsBasedRow()
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
EcelTitleId = "Split Row Numt"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", ExcelTitleId, WorkRng.Address, Type:=8)
SplitRow = Application.InputBox("Split Row Num", ExcelTitleId, 4, Type:=1)
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (WorkRng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
    xRow.Resize(resizeCount).Copy
    Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

When you run it, you’ll be asked to:

1. Specify the range to split

2. Specify the number of rows to split by

I chose 4 rows to split by and the macro returned three sheets: 4 rows, 4 rows, and 2 rows. 

However, I don’t like that the header row is only available in the first newly created sheet, while you won’t see it in the subsequent one. So, the following VBA script should solve this problem:

Sub SplitExcelSheetIntoMultipleSheetsByRowsHeader()
    Dim WorkRng As Range
    Dim xRow As Range
    Dim SplitRow As Integer
    Dim xWs As Worksheet
    Dim i As Integer, j As Integer, k As Integer
    
    On Error Resume Next
    ExcelTitleId = "Split Row Numt"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", ExcelTitleId, WorkRng.Address, Type:=8)
    SplitRow = Application.InputBox("Split Row Num", ExcelTitleId, 4, Type:=1)
    Set xWs = WorkRng.Parent
    
    Application.ScreenUpdating = False
    
    ' Get header row from the source sheet
    Dim HeaderRow As Range
    Set HeaderRow = WorkRng.Rows(1)
    
    For i = 1 To WorkRng.Rows.Count Step SplitRow
        ' Determine the number of rows to copy
        Dim numRows As Integer
        numRows = SplitRow
        If i + numRows - 1 > WorkRng.Rows.Count Then
            numRows = WorkRng.Rows.Count - i + 1
        End If
        
        ' Determine if the sheet being created is the first sheet or not
        Dim isFirstSheet As Boolean
        isFirstSheet = (i = 1)
        
        ' Create new sheet
        Dim newSheet As Worksheet
        Set newSheet = ThisWorkbook.Worksheets.Add(After:=xWs)
        
        ' Copy header row if not the first sheet
        If Not isFirstSheet Then
            HeaderRow.Copy
            newSheet.Range("A1").PasteSpecial xlPasteAll
        End If
        
        ' Copy rows to new sheet
        WorkRng.Rows(i).Resize(numRows).Copy newSheet.Range("A" & IIf(isFirstSheet, 1, 2))
    Next i
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub

And it really does but with one disclaimer – the first newly created sheet will contain the exactly specified number of rows to split by. All the subsequent sheets will contain this number (or less for the final sheet) + the header row.

How to split Excel sheets into multiple worksheets without VBA

The ways to split an Excel sheet into multiple files or worksheets that were introduced above are quite actionable, but they require you to have VBA knowledge. Of course, you can reuse my scripts and tweak them for your projects. However, sometimes you may need more of this.

So, if you want to mess around VBA and macros, you only have two options:

1. Split your Excel sheet into multiple worksheets manually.
2. Automate the splitting of your Excel sheet into multiple worksheets or files when importing your data into Excel.

Split an Excel sheet manually

You’ve probably been doing this before, so no additional explanation is needed here. The essence of this option is that you manually create as many sheets or files as you need, and copy data from your source worksheet. 

This primitive way of data partitioning works well for small datasets. However, if you manage larger ones that count more than 100 rows, then you should not rely on the manual data partition.

Do you need to automate the split of Excel or Google Sheets? 

Having the capability to automate the splitting of data in a spreadsheet would be a game changer. This way you’d get a built-in solution to transform your data when importing it into Excel or Google Sheets, or another destination with your integration solution, like Coupler.io. This data automation and analytics platform, allows you to:

• Combine data from multiple accounts or even other data sources.
• Preview, filter, and transform data before loading.
• Automate data load on a custom schedule. \
• Trigger events after the data load using the outgoing and incoming webhooks.
• Split data into multiple worksheets by specific attributes.

The last point is not available so far but you can change this! If you and your project have a use case related to splitting or partitioning data, share it with us. You can get a free consultancy offered by our data expert service.

What’s wrong with using VBA for splitting Excel sheets into multiple sheets?

To sum up this Excel sheet split tutorial, let’s consider a few reasons why VBA may not be the best choice for this data transformation:

• High learning curve. This is only applicable to users who are not familiar with programming or VBA. To use this option efficiently, you’ll have to learn how to write and debug the code to ensure it’s working. 
• Data loss and corruption. While I was debugging the VBA code samples for this article, I had a few cases when the VBA macro corrupted the data in the source sheet. So, you should be very careful with using VBA macros to avoid data loss or corruption.
• Desktopness. I use this word to describe another downside of using VBA to split Excel sheets into multiple worksheets or files. VBA can only be run on Excel desktop files. So, you won’t be able to run your macro in the cloud which sort of limits your data management capabilities.

Despite all these disadvantages, VBA remains the only actionable option to split Excel sheets into multiple sheets, apart from doing this manually. At the same time, if you’d like to have this functionality available then Coupler.io can help with a custom solution for your needs. Sign up and let us know, and we will be in touch to discuss. Good luck!