Excel Tips: Split data into multiple worksheets / workbooks based on column value
When managing large datasets in Excel, it can be highly beneficial to split data into multiple worksheets based on specific column values. This method improves not only the organization of data but also enhances readability and facilitates easier data analysis.
Suppose you have a large sales record containing multiple entries such as the product name, the quantity sold of the first quarter. The goal is to split this data into separate worksheets based on each product name so that individual sales performance can be analyzed separately.
Split data into multiple worksheets based on column value
Split data into multiple workbooks based on column value with VBA code
Split data into multiple worksheets based on column value
Normally, you can sort the data list first, and then copy and paste them one by one into other new worksheets. But this will need your patience to copy and paste repeatedly. In this section, we will introduce two straightforward methods to efficiently tackle this task in Excel, saving you time and reducing the potential for errors.
Split data into multiple worksheets based on column value with VBA code
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
Sub Splitdatabycol()
'updateby Extendoffice
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
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
Dim xWS As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) 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
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
xWS.Name = myarr(i) & ""
Else
xWS.Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
xWS.Paste Destination:=xWS.Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWS.Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub3. Then, press F5 key to run the code, and a prompt box is popped out to remind you select the header row, and then, click OK. See screenshot:
4. In the second prompt box, please select the column data that you want to split based on, then, click OK. See screenshot:
5. All data in the active worksheet is divided into multiple worksheets based on the column values. The resulting worksheets are named according to the values in the split cells and are placed at the end of the workbook. See screenshot:
Split data into multiple worksheets based on column value with Kutools for Excel
Kutools for Excel brings smart feature – Split Data right into your Excel environment. Splitting data into multiple worksheets is no longer a challenge. Our intuitive tool automatically divides your dataset based on the chosen column value or rows count, ensuring that each piece of information is exactly where you need it. Say goodbye to the tedious task of manually organizing your spreadsheets and embrace a faster, error-free way to manage your data.
After installing Kutools for Excel, select the data range, and then click Kutools Plus > Split Data to open the Split Data into multiple worksheets dialog box.
- Select Specific column option in the Split based on section, and choose the column value which you want to split the data based on from the drop-down list.
- If your data has headers and you want to insert them into each new split worksheet, please check My data has headers option. (You can specify the number of header rows based on your data. For example, if your data contains two headers, please type 2.)
- Then you can specify the split worksheet names, under the New worksheets name section, specify the worksheet names rule from the Rules drop down list, you can add the Prefix or Suffix for the sheet names as well.
- Click the OK button. See screenshot:
Now, the data in the worksheet is split into multiple worksheets in a new workbook.
Split data into multiple workbooks based on column value with VBA code
Occasionally, rather than dividing data into multiple worksheets, it may be more beneficial to split the data into separate workbooks based on a key column. Here's a step-by-step guide on how to use VBA code to automate the process of splitting data into multiple workbooks based on a specific column value.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
Sub SplitDataByColToWorkbooks()
' Updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWS As Workbook
Dim savePath As String
' Set the directory to save new workbooks
savePath = "C:\Users\AddinsVM001\Desktop\multiple files\" ' Modify this path as needed
Application.DisplayAlerts = False
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.Address(False, False)
titlerow = xTRg.Row
ws.Columns(vcol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Cells(1, ws.Columns.Count), Unique:=True
myarr = Application.Transpose(ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).Value)
ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).ClearContents
For i = 2 To UBound(myarr)
Set xWS = Workbooks.Add
ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i)
ws.Range("A" & titlerow & ":A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy
xWS.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
xWS.SaveAs Filename:=savePath & myarr(i) & ".xlsx"
xWS.Close SaveChanges:=False
Next i
ws.AutoFilterMode = False
Application.DisplayAlerts = True
ws.Activate
End Sub
3. Then, press F5 key to run the code, and a prompt box is popped out to remind you select the header row, and then, click OK. See screenshot:
4. In the second prompt box, please select the column data that you want to split based on, then, click OK. See screenshot:
5. After splitting, all data in the active worksheet is divided into multiple workbooks based on the column values. All the split workbooks are saved into the folder you specified. See screenshot:
Related Articles:
- Split data into multiple worksheets by rows count
- Efficiently dividing a large data range into multiple Excel worksheets based on a specific row count can streamline data management. For instance, splitting a dataset every 5 rows into multiple sheets can make it more manageable and organized. This guide offers two practical methods to accomplish this task swiftly and easily.
- Merge two or more tables into one based on key columns
- Supposing you have three tables in a workbook, now, you want to merge these tables into one table based on the corresponding key columns to get the result as below screenshot shown. This may be a troublesome task for most of us, but, please don’t worry, this article, I will introduce some methods for solving this problem.
- Split Text Strings by Delimiter into Multiple Rows
- Normally, you can use the Text to Column feature to split cell contents into multiple columns by a specific delimiter, such as comma, dot, semicolon, slash, etc. But, sometimes, you may need to split the delimited cell contents into multiple rows and repeat the data from other columns as below screenshot shown. Do you have any good ways for dealing with this task in Excel? This tutorial will introduce some effective methods to complete this job in Excel.
- Split multiline cell contents into separated rows/columns
- Supposing you have multiline cell content which is separated by Alt + Enter, and now you need to split the multiline contents to separated rows or columns, what can you do? In this artical, you will learn how to quickly split multiline cell contents into separated rows or columns.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
