Split Data into Multiple Worksheets in Excel (with Quick Steps)

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.

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

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 1: Create a New Macro in VBA Module

• Choose the Developer tab.
• Now, click on Record Macro from the commands.

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

• 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 2: Save the File in XLSM Format and Run the Macro

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

• After inputting the number press OK.
• We will get data into different sheets now.

• We can see that data are in the S-1, S-2, and S-3 sheets now.
• Open the S-1 sheet.

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

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

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

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

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")

DS.AutoFilterMode = False

DS.Activate

Application.ScreenUpdating = True

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)