Shamima
A large dataset may contain multiple values based on the same column. If you want you can split the same categories’ (Department, Month, Region, States, etc.) values or your preferences into different worksheets or workbooks. In this article, I’m going to explaining, how to split 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.
Download to Practice
Ways to Split Excel Sheet into Multiple Worksheets
1. Using Filter and Copy
From any sheet, you can split the data into multiple sheets by using Filter.
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. Split Excel Sheet Based on Row Count Using VBA
Before beginning with the procedure, you need to remember that you have to start data from the first rows.
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.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
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. Split Excel Sheet into Multiple Workbook Based on Column
Before starting with the procedure, you need to remember that you have to start data from the first row and first column.
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
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("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.
