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.
- 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.
- First, go to the Home tab.
- Choose the Developer tab.
- Now, click on Record Macro from the commands.
- Get a new window named 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
- 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 that data will be split into different sheets.
- 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.
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.
- 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)