How to Split Data into Multiple Worksheets in Excel

Excel is very useful when we work with a big amount of data. Sometimes we need to split those data into different sheets as per requirement. In this article, we will discuss how to split data into multiple Excel worksheets.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Method to Split Data into Multiple Worksheets

We will split the data of a sheet into different worksheets using VBA & Macros. In the data set, we’re showing data of student names, IDs, and sections.

Split Data Into Multiple Worksheets Based On Column

We will split data into different worksheets based on the column.

Step 1:

• We copy the data and paste it to another sheet at Cell A1.
• To perform this method, we always need to start data from Cell A1.

Step 2:

• First, go to the Home tab.
• Choose the Developer tab.
• Now, click on Record Macro from the commands.

Step 3:

• Get a new window named Record Macro.
• Put Split_Data as the name.
• Then, click OK.

Step 4:

• Now, write the below code on the command module of VBA.

Sub Split_Data()
Dim L As Long
    Dim DS As Worksheet
    Dim VCL, X As Integer
    Dim XCL As Long
    Dim MARY As Variant
    Dim title As String
    Dim titlerow As Integer
    Application.ScreenUpdating = False
    VCL = Application.InputBox(prompt:="Which column would you like to filter by?", title:="Filter column", Type:=1)
    Set DS = ActiveSheet
    L = DS.Cells(DS.Rows.Count, VCL).End(xlUp).Row
    title = "A1"
    titlerow = DS.Range(title).Cells(1).Row
    XCL = DS.Columns.Count
    DS.Cells(3, XCL) = "Unique"
    For X = 2 To L
        On Error Resume Next
        If DS.Cells(X, VCL) <> "" And Application.WorksheetFunction.Match(DS.Cells(X, VCL), DS.Columns(XCL), 0) = 0 Then
            DS.Cells(DS.Rows.Count, XCL).End(xlUp).Offset(1) = DS.Cells(X, VCL)
        End If
    Next
    MARY = Application.WorksheetFunction.Transpose(DS.Columns(XCL).SpecialCells(xlCellTypeConstants))
    DS.Columns(XCL).Clear
    For X = 2 To UBound(MARY)
        DS.Range(title).AutoFilter field:=VCL, Criteria1:=MARY(X) & ""
        If Not Evaluate("=ISREF('" & MARY(X) & "'!A1)") Then
            Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = MARY(X) & ""
        Else
            Sheets(MARY(X) & "").Move after:=Worksheets(Worksheets.Count)
        End If
        DS.Range("A" & titlerow & ":A" & L).EntireRow.Copy Sheets(MARY(X) & "").Range("A4")
    Next
    DS.AutoFilterMode = False
    DS.Activate
    Application.ScreenUpdating = True
End Sub

Step 5:

• Then press F5 to run the code.
• A dialogue box will appear to input a number.

Put 1 here, as we want to split data based on Column 1.

Step 6:

• After inputting the number press OK.
• We will that data will be split into different sheets.

Step 7:

• We can see that data is split into S-1, S-2, and S-3 sheets.
• Open the S-1 sheet.

On the S-1 sheet, all data regarding the S-1 section is stored here.

Here, data started from Cell A4 because we set this condition on the VBA code.

Read More: Excel Macro to Split Data into Multiple Files (With Simple Steps)

Conclusion

In this article, we described a method to explain how to split data into multiple worksheets in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Related Articles

• How to Split Data in Excel (5 Ways)
• Split Comma Separated Values into Rows or Columns in Excel
• Split Data into Equal Groups in Excel (3 Methods)
• How to Split Data into Multiple Columns in Excel
• Excel Macro to Split a Cell into Multiple Rows (With Easy Steps)
• How to Split Data from One Cell into Multiple Rows in Excel (3 Methods)