How To Quickly Delete All Text Boxes In E�l?

درباره سایت

How To Quickly Delete All Text Boxes In E�l?

آخرین مطالب

امکانات وب

جدا کردن یک شیت به چند شیت بر اساس اطلاعات یک ستون

How to Split an Excel Sheet into Multiple Worksheets

Excel

Zakhar Yung

11min read

Jan 24, 2024

Table of Contentshide

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

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

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

How to split Excel sheet into multiple worksheets based on rows

How to split Excel sheets into multiple worksheets without VBA

Split an Excel sheet manually

How to automate data import to Excel

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

When we talk about splitting data in a spreadsheet, it means data partitioning – dividing a dataset into smaller subsets. These are extracted based on a shared attribute of a column (vertical split) or row (horizontal split).

Excel, despite its supremacy in functionality, doesn’t offer any functions for splitting data. So, to split an Excel sheet into multiple worksheets, you’ll either need to do this manually or with 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!

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

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.

As an example for this tutorial, I took a dataset exported from Pipedrive with Coupler.io, a data automation and analytics platform. This dataset has an attribute for data split – 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.

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

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

objSheet.Columns("A:F").AutoFit

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

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

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)

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.

How to automate data import to Excel

How do you transfer data to Excel before splitting it? It usually takes huge amounts of time to copy and paste your data into a worksheet. The same goes for creating a VBA Macro, which requires a bunch of skills and effort to make it work. So automating this process would be a wiing bet, not only saving your time but also preventing human error. For example, you can use Coupler.io to automatically import data from over 50 source applications to Excel.

Try importing your data right here: choose your source app from a list and click Proceed. You can sign up with your Microsoft account for free.

This data automation and analytics platform allows you to:

Combine data from multiple accounts or sources.

Visualize your data in popular BI tools like Power BI.

Preview, filter, and transform data before loading.

Trigger events after the data load.

Automate data load on a custom schedule.

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!

Zakhar Yung

A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries