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.
Split data into multiple worksheets based on specific column or rows count in worksheet:
If you want to split a large worksheet into multiple sheets based on specific column data or rows count, the Kutools for Excel's Split Data feature can help you to solve this task quickly and easily.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
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 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 vcol = 1 Set ws = Sheets("Master sheet") lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row title = "A1:C1" 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 Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & "" 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 End Sub
Note: In the above code:
- vcol =1 , the number 1 is the column number that you want to split the data based on.
- Set ws = Sheets("Master sheet"), Master sheet is the sheet name that you want to apply this code.
- title = "A1:C1" , A1:C1 is the range of the title.
All of them are variables, you can change them as your need.
3. Then press F5 key to run the code, all data in the active worksheet are 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.
As an Excel beginner, this long VBA code is somewhat difficult for us, and most of us even don't know how to modify the code as our need.
Here, I will introduce you a multifunctional tool--Kutools for Excel, its Split Data utility not only can help you to split data into multiple worksheets based on column, but also can split data by rows count.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
If you have installed Kutools for Excel, please do as follows:
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 60 days. Download the free trial now!
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoI have used this VBA code, its split the data but split whole data in new sheet instead of unique value.
To post as a guest, your comment is unpublished.· 3 months agoThe VBA code split the data perfectly but its split the whole data instead of unique value.
To post as a guest, your comment is unpublished.· 3 months agoThe codes works perfectly, only trouble is that it does not separate columns when the text is too long, I have text with 40 characters and I get an empty "sheet2", every other text gets sorted.
Any ideas on how to fix it? I've already tried changin variable and placed them as Long, however I am not sure I fully understand the program. all this for the VBA option.
To post as a guest, your comment is unpublished.· 4 months agoI need a macro for following condition
suppose i have customer excel file in which first 7 rows is for header so, from 8th row records are start
i need to split rows of 500 record each in one file and save them with name customer1,customer2,customer3,........
suppose i have customer file of 2540 records so it split in
customer1 which have header rows with record starts from 8th row to 507th row
customer2 which have header rows with record starts from 508th row to 1007th row
customer3 which have header rows with record starts from 1008th row to 1507th row
customer4 which have header rows with record starts from 1508th row to 2007th row
customer5 which have header rows with record starts from 2008th row to 2507th row
customer6 which have header rows with record starts from 2508th row to 2540th row
To post as a guest, your comment is unpublished.· 4 months agoHow can I get the Total of Column C for each sheets.?