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

Here’s an overview of splitting an Excel sheet into multiple worksheets.

We’ll use a sample dataset representing the sales information of different months.

Method 1 – Using Filter and Copy to Split an Excel Sheet into Multiple Worksheets

Steps:

• Select the cell range where you want to apply the Filter. We selected the cell range B3:E15.
• Open the Data tab and select Filter.
• You also can use CTRL + SHIFT + L to apply Filter using the keyboard.

• A Filter is applied to the selected cell range.
• Click on the Month column since that’s what we’re using to split the sheet.
• We deselected everything except January.
• Click OK.

• All the values where the Month is January are filtered.
• Copy the data and Paste it into a new worksheet.

• We named the new sheet January. Thus, you will see all sales information for January is presented here. 

• Repeat the process for other months.

• Copy the data and paste it into new worksheets.

• We named the second sheet February.

• Repeat the filtering process for March.

• Copy the data and paste it into the new worksheet.

• We named the new sheet March.

Method 2 – Splitting an Excel Sheet into Multiple Worksheets Based on Row Count Using VBA

Steps:

• Make the dataset start from the first row and remove column headers.
• Open the Developer tab and select Visual Basic.

• You’ll get the window for Microsoft Visual Basic for Applications.
• From Insert, select Module.

• A Module will open.
• Insert 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

We’ve created a sub-procedure called SplitExcelSheet_into_MultipleSheets.

We declared a couple of variables these are WorkRng and xRow as Range type then SplitRow as Integer also xWs as Worksheet type.

We used ExcelTitleId to give the dialog box a title.

We’ve provided split row number 4 to split data by 4 rows because in my dataset the Month of January has 4 rows.

We used a For loop to SplitRow until the given cell range ends.

• Save the code and go back to the worksheet.
• Open the Developer tab.
• From Insert, select Button

• A dialog box will pop up. Select SplitExcelSheet_into_Multiplesheets from Macro Name and click OK.

• Click on the button to run the macro.

• A dialog box will pop up where you can put the data range. We selected the cell range B1:E12
• 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. We put 4 as Split Row Num.

• You’ll get three 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.

Method 3 – Splitting an Excel Sheet into Multiple Workbooks Based on Column

Steps:

• Move the data so it starts from A1.
• Open the Developer tab and select Visual Basic.

• From Insert, select Module

• A Module will open.
• TInsert 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

• Save the code and go back to the worksheet.
• Open the View tab.
• From Macros, select View Macros

• A dialog box will pop up.

• From Macro name select SplitSheetIntoMultipleWorkbooksBasedOnColumn, and select the workbook within Macros in.
• Run the selected macro.
• You will see 3 new workbooks have been created as there are 3 different Months in column C. The Book1 is for January.

• The Book2 is for February.

• The Book3 is for March.

Download the Practice Workbook

Related Articles

• How to Split Sheet into Multiple Sheets Based on Rows in Excel
• How to Split Excel Sheet into Multiple Sheets Based on Column Value
• How to Split Excel Sheet into Multiple Files
• How to Enable Side-by-Side View with Vertical Alignments in Excel
• Excel VBA: Split Sheet into Multiple Sheets Based on Rows
• How to Split a Workbook to Separate Excel Files with VBA Code

<< Go Back to Split Excel Cell | Excel Worksheets | Learn Excel