How to split data into multiple worksheets based on column in Excel?
Supposing you have a worksheet with huge rows of data, and now, you need to split the data into multiple worksheets based on the Name column (see following screenshot), and the names are entered randomly. Maybe you can sort them 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. Today, I will talk about some quick tricks to solve this task.
If you want to split the data based on column value quickly and automatically, the following VBA code is a good choice. Please do as this:
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 Sub
3. Then, press F5 key to run the code, and a prompt box is popped out to remind you select the header row, see screenshot:
4. And then, click OK button, and in the second prompt box, please select the column data that you want to split based on, see screenshot:
5. Then, click OK, and all data in the active worksheet is split into multiple worksheets by the column value. And the split worksheets are named with the split cell names. See screenshot:
Note: The split worksheets are placed in the end of the workbook where the master worksheet is in.
After installing Kutools for Excel, please do as this:
1. Select the range of data that you want to split.
2. Click Kutools Plus > Worksheet > Split Data, see screenshot:
3. In the Split Data into Multiple Worksheets dialog box, you need to:
1). Select Specific column option in the Split based on section, and choose the column value which you want to split the data based on in 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.)
2). Then you can specify the split worksheet names, under the New worksheets name section, specify the worksheet names rules from the Rules drop down list, you can add the Prefix or Suffix for the sheet names as well.
3). Click the OK button. See screenshot:
4. Now the data are split into multiple worksheets in a new workbook.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Download the free trial now!
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!