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:
- Specify the range to split

- 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:
- Split your Excel sheet into multiple worksheets manually.
- 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!
Back to Blog