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 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
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!
