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.

Data Set to Split Data into Multiple Excel Worksheets

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

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

Split Data Into Multiple Worksheets Based On Column

  • 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

Split Data Into Multiple Worksheets Based On Column


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.

Split Data Into Multiple Worksheets Based On Column

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

Code Explanation:

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

>> Declare different variables.

Application.ScreenUpdating = False

>> This command defines whether the screen will update or not. Here, False stops updating. Screen update is turned off to speed up the macro.

VCL = Application.InputBox(prompt:="Which column would you like to filter by?", title:="Filter column", Type:=1)

>> This command introduces an input box. We will input the column number in the input box.

Set DS = ActiveSheet

>> Fixed active sheet as the value of the DS variable.

L = DS.Cells(DS.Rows.Count, VCL).End(xlUp).Row

>> DS.Cells(DS.Rows.Count, VCL) defines that our reference is a cell of column VCL located at the last row of the DS sheet. And, End(xlUp) choose the last or first row in the upward direction.

titlerow = DS.Range(title).Cells(1).Row

>> Indicate the first row as the title.

XCL = DS.Columns.Count

>> Count the number of columns into the XCL variable.

DS.Cells(3, XCL) = "Unique"

>> Name the range as the “Unique

For X = 2 To L

>> Use For loop and set the value of X from 2 to L

On Error Resume Next

>> If any error is found resume the operation and go to the Next section.

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

>> If a condition is applied here. The worksheet Match function is used to set the condition.

MARY = Application.WorksheetFunction.Transpose(DS.Columns(XCL).SpecialCells(xlCellTypeConstants))

>> We have used the worksheet Transpose function in the line.

DS.Columns(XCL).Clear

>> This is used to clear the contents of the Column marked by the XCL variable.

For X = 2 To UBound(MARY)

>> A For loop is applied here. We set the value of the X from 2 to the upper limit of the array MARY variable.

DS.Range(title).AutoFilter field:=VCL, Criteria1:=MARY(X) & ""

>> Set the VCL variable as the range of AutoFilter.

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

>> We set another IF condition.

DS.Range("A" & titlerow & ":A" & L).EntireRow.Copy Sheets(MARY(X) & "").Range("A4")

>> Copy the data of the whole row.

DS.AutoFilterMode = False

>> This command removes the arrow symbol of the filter drop-down.

DS.Activate

>> Activates the worksheet of the DS variable.

Application.ScreenUpdating = True

>> Enable screen updating by set the value 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

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.

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

    • We are extremely sorry Hannah W. to know that you have faced this difficulty in applying our provided code! We have added the code explanation. Is your problem solved? Please let us know.
      With regards
      -ExcelDemy team

  2. Not worked

  3. Works great, thank you! How do you remove the InputBox and make it always select Column 1?

    • Hi JEFF! Thanks for your nice compliment. To remove the InputBox and make the code always select Column 1, just remove the InputBox command and variable VCL. After that, replace the VCL with 1.
      You can directly use the following code:

      Sub Split_Data()
      Dim L As Long
      Dim DS As Worksheet
      Dim XCL As Long
      Dim MARY As Variant
      Dim title As String
      Dim titlerow As Integer
      Application.ScreenUpdating = False
      Set DS = ActiveSheet
      L = DS.Cells(DS.Rows.Count, 1).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, 1) <> "" And Application.WorksheetFunction.Match(DS.Cells(X, 1), DS.Columns(XCL), 0) = 0 Then
      DS.Cells(DS.Rows.Count, XCL).End(xlUp).Offset(1) = DS.Cells(X, 1)
      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:=1, 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

  4. I’m getting the same error. I’d love to know what I’m doing wrong.

    • Dear HEATHER, I guess you are facing the same problem as HANNAH. But we have rechecked the code, and applied it again, but have found no issue.
      Can you please send us your problem with specific details? Here is our address: [email protected]
      We have added the code explanation. Have a look at it and let us know if it can do any help regarding your problem. But the best will be to send your problem in detail with your Excel file. Thanks and regards.
      -ExcelDemy Team

  5. Reply
    Juan Martin Opacak Nov 24, 2022 at 8:08 PM

    Works great.
    However, would it be possible to change the names of the newly created sheets?
    Using your example, the generated sheets were named “S-1”, “S-2”, “S-3”.
    How would you modify the code to set them to “Section S-1”, “Section S-2”, “Section S-3”? (where ‘Section’ is a text value copied from cell A1 – or any other)
    Thank you!

    • Hi JUAN MARTIN OPACAK,
      Thanks for reading our articles. We have found a solution to fulfill your requirement. You need to insert the following macros with the existing VBA code.

      Worksheets(ActiveSheet.Index + 1).Select
      ActiveSheet.Name = (Worksheets(2).Range("A1").Value) & " " & ActiveSheet.Name

      Here, Worksheets(2).Range(“A1”).Value defines we want to insert the text of Cell A1 of Worksheet 2 with the current sheet name.
      Have a look at the image below, where to insert the mentioned VBA code.
      Split Data

Leave a reply

ExcelDemy
Logo