# How to Split Excel Sheet into Multiple Worksheets (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

A large dataset may contain multiple values based on the same column. If you want you can split the same categories’ (Department, Month, Region, State, etc.) values or your preferences into different worksheets or workbooks. In this article, I’m going to explain 3 suitable methods to split Excel sheet into multiple worksheets. Before that, have a look at the overview of splitting Excel sheet into multiple worksheets. To make this explanation clearer to you, I’m going to use a sample dataset. There are 4 columns in the dataset representing sales information of different months. These columns are Sales Person, Region, Month, and Sales. ## 1. Using Filter and Copy to Split Excel Sheet into Multiple Worksheets

From any sheet, you can split the data into multiple sheets by using Filter.

Steps:

• First, select the cell range where you want to apply the Filter. Here, I selected the cell range B3:E15.
• Then, open the Data tab >> select Filter.
• You also can use CTRL + SHIFT + L to apply Filter using the keyboard. • Now, Filter is applied to the selected cell range.
• Next, click on the Month column as I want to split data depending on Month values.
• From there I deselected everything except January. Finally, click OK. • Now, all the values where the Month is January are filtered.
• Then, Copy the data and Paste it into the new worksheet. • Here, I named the new sheet January. Thus, you will see all sales information for January is presented here. • For the rest of the Months, you can follow the same procedures.
• Again, click on the Month column as I want to split data depending on Month values.
• From there deselect everything except February. Finally, click OK. • Now, all the values for February Month are filtered.
• Then, copy the data and paste it into the new worksheet. • Later, I named the new sheet February. Thus, you will see all sales information for the Month of February is presented here. • Again, click on the Month column as I want to split data depending on Month values.
• From there deselect everything except March. Finally, click OK. • Now, you will see all the values of March are filtered.
• Then, copy the data and paste it into the new worksheet. • In the end, I named the new sheet March. Hence, you will see all sales information for March is presented here. ## 2. Splitting Excel Sheet into Multiple Worksheets Based on Row Count Using VBA

Furthermore, you can use VBA Macro Editor to split the sheets. Before beginning with the procedure, you need to remember that you have to start data from the first rows.

Steps:

• Now, open the Developer tab >> select Visual Basic. • It will open a new window of Microsoft Visual Basic for Applications.
• Now, from Insert >> select Module • A Module will open there.
• Then, write the following code in the Module.
``````Sub SplitExcelSheet_into_MultipleSheets()
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.ActiveSheet.Range("A1").PasteSpecial
Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub`````` • Here, I’ve created a sub-procedure called SplitExcelSheet_into_MultipleSheets.
• Where I declared a couple of variables these are WorkRng and xRow as Range type then SplitRow as Integer also xWs as Worksheet type.
• Also, used ExcelTitleId to give the dialog box title.
• I’ve provided split row number 4 to split data by 4 rows because in my dataset the Month of January has 4 rows.
• Lastly, used a For loop to SplitRow until the given cell range ends.
• Then, save the code and go back to the worksheet.
• Now, open the Developer tab >> From Insert >> select Button • A dialog box will pop up.
• To assign the macro in the inserted button.
• Select SplitExcelSheet_into_Multiplesheets from the Macro Name then click OK. • Just click on the button to run the macro. • Now, a dialog box will pop up where you can put the data range.
• Here, I selected the cell range B1:E12
• Then, click OK. • Another dialog box will pop up to show you the selected row count you already provided in the code to split the dataset.
• In the code, I provided 4 as Split Row Num • As I’ve in total 12 rows so with 4 rows there will be 3 sheets. • In Sheet1, you will see the data of the first 4 rows. • In Sheet2, you will see the data of rows 5 to 8. • In Sheet3, you will see the data of the last 4 rows. ## 3. Splitting Excel Sheet into Multiple Workbooks Based on Column

Before starting the procedure, you must remember that you have to start data from the first row and first column.

Steps:

• Now, open the Developer tab >> select Visual Basic. • It will open a new window of Microsoft Visual Basic for Applications.
• Now, from Insert >> select Module • A Module will open there.
• Then, write the following code in the Module.
``````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("C" & nRow).Value
If objDictionary.Exists(strColumnValue) = False Then
End If
Next
varColumnValues = objDictionary.Keys
For i = LBound(varColumnValues) To UBound(varColumnValues)
varColumnValue = varColumnValues(i)
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("C" & 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:D").AutoFit
End If
Next
Next
End Sub`````` • Here, I’ve created a sub-procedure called SplitSheetIntoMultipleWorkbooksBasedOnColumn, where I declared multiple variables.
• I used 3 For loops. The 1st For loop will count the rows from row 2 to the last row with value to get the specific column. I have given the instance of “C” column. You can change it to your case.
• The 2nd For loop will create a new Excel workbook.
• The 3rd For loop will copy the data with the same column “C” value to the new workbook from 2nd row to last row with value.
• Then, save the code and go back to the worksheet.
• Now, open the View tab >> From Macros >> select View Macros • A dialog box will pop up. • Now, from the Macro name select the SplitSheetIntoMultipleWorkbooksBasedOnColumn also select the workbook within Macros in.
• Finally, Run the selected macro.
• Finally, you will see 3 new workbooks have been created as there are 3 different Months in column C. The Book1 for January. • The Book2 for the February. • The Book3 for March. ## Conclusion

In this article, I’ve explained 3 ways of how to split Excel sheet into multiple worksheets. You can follow any of the explained ways to split your Excel sheet into multiple worksheets. In case you have any confusion or question regarding these methods you may comment down below.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

1. Reply Hi Shamima! Thank you for your hard work! I have been searching a tool for cutting my big excel file and your last code saved my life! Thank you so much!

• Reply Hi A,

2. Reply Hi Shamima, thanks for this and could I clarify something? Using method 3 to split via column value, if I need to run the macro after the first use (wherein the macro creates the new sheets) to account for new data does the macro just update the sheets it created in the first use?

M.

• Reply Hello Michelle,
Hope you are doing well. If you need to run the macro after the first use it will create new sheets with the updates you made on your dataset.

It won’t automatically update the workbooks created previously (Book1, Book2, and Book3) rather it will create Book4, Book5, and Book6 with the updated dataset.

Thanks
Shamima Sultana

3. Reply Hi SHAMIMA,

when I used the code for a smaller sheet with the smaller split numbers it works perfectly but I need to split a file that include 1M records into 10 sheets that include 100,000 records. Do you know how can I achieve that?

• Reply Tanjima Hossain Aug 29, 2022 at 3:22 PM

According to your requirement, I have created a random dataset containing 1M records within 1M rows of a dataset in Excel. Using a VBA code, I will split it into 10 different worksheets each containing 100,000 rows. • Type the following VBA code. Here, instead of using the InputBox method, we are directly declaring the total range and the split number in the code which will expediate running the code.

``````Sub SplitExcelSheet_into_MultipleSheets()
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
Set xWs = Range("A1:A1000000").Parent
Set xRow = Range("A1:A1000000").Rows(1)
Application.ScreenUpdating = False
For i = 1 To 1000000 Step 100000
resizeCount = 100000
If (Range("A1:A1000000").Rows.Count - xRow.Row + 1) < 100000 Then resizeCount = Range("A1:A1000000").Rows.Count - xRow.Row + 1
xRow.Resize(resizeCount).Copy
Application.ActiveSheet.Range("A1").PasteSpecial
Set xRow = xRow.Offset(100000)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub`````` • Press F5.
In this way, we have created 10 different sheets each with 100000 records. 4. Reply Thanks, it works perfectly. you are the best

• Reply Tanjima Hossain Sep 4, 2022 at 9:20 AM

5. Reply Jeff Blackwell Sep 2, 2022 at 7:44 PM

Hi! This is great! Is there a way to split the worksheet into separate sheets within the same workbook based on column?

• Reply Tanjima Hossain Sep 4, 2022 at 11:10 AM

Hi Jeff Blackwell,
Thanks for reaching us. You can use the following code with some adjustments to split a worksheet based on column into some worksheets of the same workbook.
Here, we will split the following worksheet based on the Month column and so the worksheet will be split into 3 different sheets – January, February, March. • Use the following code. Adjust the starting row number in nRow = 2, the column indicating letter in (the base column on which you will split the worksheet) objWorksheet.Range(“C” & nRow).

``````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 xWS 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("C" & nRow).Value
If objDictionary.Exists(strColumnValue) = False Then
End If
Next
varColumnValues = objDictionary.Keys
For i = LBound(varColumnValues) To UBound(varColumnValues)
varColumnValue = varColumnValues(i)
objWorksheet.Rows(1).EntireRow.Copy
xWS.Name = varColumnValue
xWS.Activate
xWS.Range("A1").Select
xWS.Paste
For nRow = 2 To nLastRow
If CStr(objWorksheet.Range("C" & nRow).Value) = CStr(varColumnValue) Then
objWorksheet.Rows(nRow).EntireRow.Copy
nNextRow = xWS.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1
xWS.Range("A" & nNextRow).Select
xWS.Paste
xWS.Columns("A:D").AutoFit
End If
Next
Next
End Sub`````` • Press F5.
Then, you will have 3 sheets- January, February, March. 6. Reply Hi,

Good day,

I have a problem when split the data . for example I use sheet name as Arrow Electronics Asia (S) Pte. Ltd. and its prompt error due to this reason:

type the invalid for sheet/chart
1. The name thatyou type doest not exceed 31 character.
2. The name does not contain any of the following character:/
3.You did not leave the name blank.

Debug highlighted this code : xWS.Name = varColumnValue

VBA code:

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 xWS 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(“C” & nRow).Value
If objDictionary.Exists(strColumnValue) = False Then
End If
Next
varColumnValues = objDictionary.Keys
For i = LBound(varColumnValues) To UBound(varColumnValues)
varColumnValue = varColumnValues(i)
objWorksheet.Rows(1).EntireRow.Copy
xWS.Name = varColumnValue
xWS.Activate
xWS.Range(“A1”).Select
xWS.Paste
For nRow = 2 To nLastRow
If CStr(objWorksheet.Range(“C” & nRow).Value) = CStr(varColumnValue) Then
objWorksheet.Rows(nRow).EntireRow.Copy
nNextRow = xWS.Range(“A” & objWorksheet.Rows.Count).End(xlUp).Row + 1
xWS.Range(“A” & nNextRow).Select
xWS.Paste
xWS.Columns(“A:D”).AutoFit
End If
Next
Next
End Sub

May I know how to fix the error since i have no issues when save the file with other name.

• Reply Hello, AMIRA!
Thanks for sharing your problem with us!
The code works properly for me. Can you please send me your excel file via email? ([email protected]).
So that, I can solve your problem.

Good Luck!

Regards,
Sabrina Ayon
Author, ExcelDemy
.

7. Reply Neil Mastellone Jul 13, 2023 at 7:25 PM

This is so incredibly helpful!!! thank you!!! I almost feel like an ingrate asking for anything more, but here goes. Is there a way to choose the name of the resultant files? Like in your example, could the resultant files have been called January.xls, February.xls, etc.? How would you automate the naming process?

• Reply Hello Neil,

Yes, there is a way to name the resultant files using Excel VBA. I am modifying the VBA code to split the column into multiple workbooks given above. This code will rename the new files as month names using monthNames () array.

Here is the modified code:

``````
Sub SplitSheet_IntoMultipleWorkbooks_BasedOnColumn_withnames()
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("C" & nRow).Value

If objDictionary.Exists(strColumnValue) = False Then
End If
Next

varColumnValues = objDictionary.Keys

Dim monthNames() As String
monthNames = Split("January,February,March,April,May,June,July,August,September,October,November,December", ",")

For i = LBound(varColumnValues) To UBound(varColumnValues)
varColumnValue = varColumnValues(i)

Set objSheet = objExcelWorkbook.Sheets(1)
objSheet.Name = monthNames(i Mod 12) & ".xls"

objWorksheet.Rows(1).EntireRow.Copy
objSheet.Activate
objSheet.Range("A1").Select
objSheet.Paste

For nRow = 2 To nLastRow
If CStr(objWorksheet.Range("C" & 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).Select
objSheet.Paste
objSheet.Columns("A:D").AutoFit
End If
Next

' Save the workbook with the appropriate name
objExcelWorkbook.SaveAs ThisWorkbook.Path & "\" & monthNames(i Mod 12) & ".xls"
objExcelWorkbook.Close SaveChanges:=False
Next
End Sub
``````

Steps:
1. Enter the code in a module >> close the Visual Basic window.
2. Go to the sheet you want to split >> Click on Developer >> Macros.
3. In Macro dialog, select SplitSheet_IntoMultipleWorkbooks_BasedOnColumn_withnames >> Run. 4. Consequently, the new files appear with declared names. Note: The new files are created in .xls format as you requested. You can change the format in the code if you want.

I hope this was helpful to you. Let me know if you have any further queries.

Best Regards,
Yousuf Shovon

8. Reply Hello
Change in the original sheet How changes apply to separated sheets simultaneously

• Reply Musiha Mahfuza Mukta Jul 23, 2023 at 5:50 PM

Thanks for your comment, Mojtaba. You can use VBA code which should be written in the original sheet. For example, I have a sheet named “Dataset“. I have divided this sheet into 3 sheets based on row. The names of these three sheets are Sheet5, Sheet6, and Sheet7. Now, write click on “Dataset” >> from the Context Menu Bar >> select View Code. Write the following code in VB Editor. ``````Private Sub Worksheet_Change(ByVal Target As Range)
x = Target.Value
Set MyRange = Sheets("Dataset").UsedRange.Find(x)
Y = MyRange.Row
Z = MyRange.Column
If Y > 11 Then
Target.Copy Destination:=Sheets("Sheet7").Range("A1").Cells(Y - 8, Z)
ElseIf Y > 7 Then
Target.Copy Destination:=Sheets("Sheet6").Range("A1").Cells(Y - 4, Z)
ElseIf Y > 3 Then
Target.Copy Destination:=Sheets("Sheet5").Range("A1").Cells(Y, Z)
End If
End Sub``````

Here, you must change the sheet names according to your workbook. Then you have to modify the conditions. Here, in my dataset there was 15 used rows. In the separated sheets there was 4 rows for each of them (except column headers). So, I set the conditions as row number > 11/7/3. So, when you change any cell value that value will be updated in the corresponding sheet. Like, if I change the cell value of C8 cell, then the change will be done in C4 cell of Sheet6 (as row number was 8).
So, set all the conditions properly for all sheets, then with any change of the original sheet, you will get the updated values in other sheet too.
Still, if you don’t get my point, then please comment or email us with the workbook. We will try to solve your problem.
Regards
Musiha/Exceldemy Advanced Excel Exercises with Solutions PDF  