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

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.

Sample Dataset

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.

Using Filter and Copy to Split Excel Sheet into Multiple Worksheets

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.

Using Filter and Copy to Split Excel Sheet into Multiple Worksheets

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.

Using Filter and Copy to Split Excel Sheet into Multiple Worksheets

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.

Using Filter and Copy to Split Excel Sheet into Multiple Worksheets

Now, you will see all the values of March are filtered.
Then, Copy the data and Paste it into the new worksheet.

Using Filter and Copy to Split Excel Sheet into Multiple Worksheets

In the end, I named the new sheet March. Hence, you will see all sales information for March is presented here. 

Read More: Split Excel Sheet into Multiple Sheets Based on Rows

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

Split Excel Sheet Based on Row Count Using VBA

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.

Split Excel Sheet Based on Row Count Using VBA

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.

Split Excel Sheet Based on Row Count Using VBA

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.

Split Excel Sheet Based on Row Count Using VBAIn Sheet2, you will see the data of rows 5 to 8.

In Sheet3, you will see the data of the last 4 rows.

Split Excel Sheet Based on Row Count Using VBA

Read More: Excel VBA: Split Sheet into Multiple Sheets Based on Rows


Similar Readings


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

Split Excel Sheet into Multiple Workbook Based on Column

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

Split Excel Sheet into Multiple Workbook Based on Column


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

Split Excel Sheet into Multiple Workbook Based on Column

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.

Split Excel Sheet into Multiple Workbook Based on Column

The Book3 for March.

Read More: How to Split Excel Sheet into Multiple Sheets Based on Column Value

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.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was 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.

12 Comments
  1. 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!

  2. 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.

    • 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. 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?

    • Hi Milad,
      Thanks for your question.
      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.
      1
      • 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.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
          Application.ActiveSheet.Range("A1").PasteSpecial
          Set xRow = xRow.Offset(100000)
      Next
      Application.CutCopyMode = False
      Application.ScreenUpdating = True
      End Sub

      2
      • Press F5.
      In this way, we have created 10 different sheets each with 100000 records.
      3

  4. Thanks, it works perfectly. you are the best

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

    • 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.
      1
      • 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
                 objDictionary.Add strColumnValue, 1
              End If
          Next
          varColumnValues = objDictionary.Keys
          For i = LBound(varColumnValues) To UBound(varColumnValues)
              varColumnValue = varColumnValues(i)
             Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
              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

      2
      • Press F5.
      Then, you will have 3 sheets- January, February, March.
      3

  6. 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
    objDictionary.Add strColumnValue, 1
    End If
    Next
    varColumnValues = objDictionary.Keys
    For i = LBound(varColumnValues) To UBound(varColumnValues)
    varColumnValue = varColumnValues(i)
    Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
    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.

    Thank you in advance

    • 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
      .

Leave a reply

ExcelDemy
Logo