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.

Data Set to Split Data into Multiple Excel Worksheets


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.

Split Data Into Multiple Worksheets Based On Column

Step 2:

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

Split Data Into Multiple Worksheets Based On Column

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

Split Data Into Multiple Worksheets Based On Column

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.

Split Data Into Multiple Worksheets Based On Column

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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

2 Comments
  1. I think I would need more information on how the code actually works in order to get it to work for me. I was trying it on my own data, but I keep getting an overflow error on the line: For X = 2 To L

    I swear I got it to work once, but then the excel file broke, and I lost all the progress. Just having a block of code isn’t super helpful if there aren’t notations explaining what the parts do so we know how to adjust it if need be.

  2. Not worked

Leave a reply

ExcelDemy
Logo