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

Get FREE Advanced Excel Exercises with Solutions!

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. ## Step-by-Step Procedures to Split Data into Multiple Worksheets in Excel

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

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. • Choose the Developer tab.
• Now, click on Visual Basic from the Code group. • After that, click on,

InsertModule • Now, write the below code in the Module box.
``````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
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.

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
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 Paul

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.

1. Reply 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.

• Reply 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. Reply Not worked

• Reply The code is perfectly working from our end, Goel! Can you please send your problem to this email: [email protected]?

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

• Reply 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. Reply I’m getting the same error. I’d love to know what I’m doing wrong.

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

• Reply 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. 6. Reply I have been using a similar macro but am now noticing that I am getting additional sheets added with names like Sheet 15, Sheet 30. Any idea why? Here is the macro I’ve used.
Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer

‘This macro splits data into multiple worksheets based on the variables on a column found in Excel.
‘An InputBox asks you which columns you’d like to filter by, and it just creates these worksheets.

Application.ScreenUpdating = False
vcol = 7
Set ws = ActiveSheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = “A1”
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = “Unique”
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) “” And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next

myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear

For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & “”
If Not Evaluate(“=ISREF(‘” & myarr(i) & “‘!A1)”) Then
Else
Sheets(myarr(i) & “”).Move after:=Worksheets(Worksheets.Count)
End If
ws.Range(“A” & titlerow & “:A” & lr).EntireRow.Copy Sheets(myarr(i) & “”).Range(“A1”)
‘Sheets(myarr(i) & “”).Columns.AutoFit
Next

ws.AutoFilterMode = False
ws.Activate
Application.ScreenUpdating = True
End Sub

• Reply Dear RENEE,
Thanks for following our articles. You mentioned a problem regarding the sheet name. Your sheet name will depend on the value of vcol variable. And one thing the sheets will split depending on the value of the 1st column which is the 1st column of the dataset. And, your provided code is not working due to some mistakes. You have made a mistake by setting the of vcol 7. Set the value of vcol as 1. Furthermore, use the below VBA that will solve your problem correctly.

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer

‘This macro splits data into multiple worksheets based on the variables on a column found in Excel.
‘An InputBox asks you which columns you’d like to filter by, and it just creates these worksheets.

Application.ScreenUpdating = False
vcol = 1
Set ws = ActiveSheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = “A1”
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = “Unique”
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> “” And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next

myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear

For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & “”
If Not Evaluate(“=ISREF(‘” & myarr(i) & “‘!1)”) Then
Else
Sheets(myarr(i) & “”).Move after:=Worksheets(Worksheets.Count)
End If
ws.Range(“A” & titlerow & “:A” & lr).EntireRow.Copy Sheets(myarr(i) & “”).Range(“A1”)
‘Sheets(myarr(i) & “”).Columns.AutoFit
Next

ws.AutoFilterMode = False
ws.Activate
Application.ScreenUpdating = True
End Sub

7. Reply This macro is great, thank you for sharing it!
It works very well to split my data, however I only need to copy the lines from column A to J, not the entire row. I am very new to VBA/macros and am having trouble figuring out how to do this myself. Could you please help?

• Reply Saquib Ahmad Shuvo Mar 21, 2023 at 4:36 PM

Hello CASSIE,
Greetings. Use Excel VBA to quickly complete your task. In Excel VBA, you can use the following code to only copy the cells in columns A through J (inclusive) rather than the entire row:

``````
Sub CopyColumns()
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:J" & lastRow).Copy
End Sub
``````

The End(xlUp) method, which locates the final non-empty cell in the column, is used in this code to obtain the last row in column A first. Then, using the Range object and Copy method, it copies the data from columns A to J.

This code can be changed if you want to perform additional operations on the copied data or paste it in a different place. Advanced Excel Exercises with Solutions PDF  