Note: The other languages of the website are Google-translated. Back to English

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.

doc split data by columns 1

Split data into multiple worksheets based on column with VBA code

Split data into multiple worksheets based on column with Kutools for Excel

Split data into multiple worksheets based on column with VBA code

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:

doc split data by columns 7

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:

doc split data by columns 8

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:

doc split data by columns 2

Note: The split worksheets are placed in the end of the workbook where the master worksheet is in.

Split data into multiple worksheets based on column with Kutools for Excel

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.

Note:To apply this Split Data, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

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:

doc split data by columns 3

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:

doc split data by columns 4

4. Now the data are split into multiple worksheets in a new workbook.

doc split data by columns 5

Click to Download Kutools for Excel and free trial Now!

Split data into multiple worksheets based on column with Kutools for Excel

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Download the free trial now!

Related article:

How to split data into multiple worksheets by rows count?

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (303)
No ratings yet. Be the first to rate!
Jagadesh.K
Jagadesh.K
This comment was minimized by the moderator on the site
Split data into multiple worksheets based on column with VBA code shows some error. please try to rectify it & update the same. If you provide the sample excel files it will be really helpful.
Reply
0
0
Georges
Georges     Jagadesh.K
This comment was minimized by the moderator on the site
Hi, thanks for the code it works for me! I am trying to find a code that split one master sheet into multiple sheet based on date
Reply
0
0
Heidi
Heidi     Jagadesh.K
This comment was minimized by the moderator on the site
That was incredible! This process would have taken me over an hour to do, but it was done within 30 seconds. This one I will keep for my VBA library. Thank you!
Reply
0
0
Sarah
Sarah     Jagadesh.K
This comment was minimized by the moderator on the site
Hi, I've got 30000 cells in my worksheet and need to split them up in months. is there a code I can use to do it quicker. I have got 8 columns and date is column B. I've been playing around with the above code that is given but failing it big time. Could you please help me with this. Thanks in advance
Reply
0
0
Lok
Lok     Jagadesh.K
This comment was minimized by the moderator on the site
I am getting following error: Runtime error '6' overflow After debugging show the line For i = 2 To Ir My excel rows are over 500,000. Is there any solution Thank you very much for the code. Best Regard Lok
Reply
0
0
Lok
Lok     Jagadesh.K
This comment was minimized by the moderator on the site
Hallo, Thank you very much for the code. i am getting following error: Runtime error '6' overflow at the line For i = 2 To Ir Any solution for this. Thank you
Reply
0
0
Cindy
Cindy
This comment was minimized by the moderator on the site
I get an error when pressing F5 - GoTo Box asking for a reference??
Reply
0
0
Niki
Niki
This comment was minimized by the moderator on the site
The VBA process worked perfectly, thank you so much for sharing your expertise and saving me a lot of time!
Reply
0
0
Vickey
Vickey
This comment was minimized by the moderator on the site
The VBA code worked perfectly. It does not seem to update the sheets as changes are made to Sheet1. Please assist.
Reply
0
0
Pieter
Pieter     Vickey
This comment was minimized by the moderator on the site
Hi, Thanks for the code it works for me! I only have two questions/remarks. 1 the copied data does not inlclude the layout of the original file. Would it be possible to copy the data as table with autofilter? 2 the copied data does not seem to be bounded to the title range. Is it possbible to adjust the code for a specific range or tablename? These adjustment would be very helpfull. Regards, Pieter
Reply
0
0
Belinda Martinez
Belinda Martinez
This comment was minimized by the moderator on the site
Works like a charm! Thank you.
Reply
0
0
Bobby M
Bobby M
This comment was minimized by the moderator on the site
Works like a charm... Thank you for premium code... :lol:
Reply
0
0
Jonathan
Jonathan
This comment was minimized by the moderator on the site
Thank you so much, this worked great. However, what hapens if I want the data within each tab sorted again (using anther column)? Basically, this VBA breaks it into tabs but I may want it broken down further..is that possible?
Reply
0
0
Ben
Ben     Jonathan
This comment was minimized by the moderator on the site
Hi Jonathan, Old comment I know, but might be helpful for others in the future: I needed to do this but couldnt find an easy way with the VBA. However, I found that if you make a new Column in your Spreadsheet as an amalgamation of the 2, so eg =A1&" "&A2 This then gives you 1 Cell with both sets of information. You can then run the above module and it works fine! Edit - The data in the collumns needs to be shorter than 30 Characters, otherwise the data is not copied across (shows as an error on the Module) and you get a blank sheet in the middle of your new Sheets.
Reply
0
0
Aditi
Aditi
This comment was minimized by the moderator on the site
Awesome.. This was stunning. I was struggling with this problem for so long and this code came in as a respite. Thanks for sharing it.
Reply
0
0
Gopinath
Gopinath
This comment was minimized by the moderator on the site
Amazing. Thanks for posting.
Reply
0
0
MieMie
MieMie
This comment was minimized by the moderator on the site
The code worked like a charm for a smaller data (less than 1200 rows). I tried to use on the bigger worksheet with (17000 rows) and it just crashed after splitting into 10-12 sheets. So we tried to split the original data into 3 different workbooks and still shut us down. We have Windows 7 and our computers are not that slow either. Do you suggest any limited rows on data to use this code safely? Any suggestion would be appreciated.
Reply
0
0
Abel Solomon
Abel Solomon     MieMie
This comment was minimized by the moderator on the site
The code worked like a charm for a smaller data (less than 1200 rows). I tried to use on the bigger worksheet with (17000 rows) and it just crashed after splitting into 10-12 sheets. So we tried to split the original data into 3 different workbooks and still shut us down. We have Windows 7 and our computers are not that slow either. Do you suggest any limited rows on data to use this code safely? Any suggestion would be appreciated. What I'm not sure is: What's the max number of rows the macro can support? I could play around with it... It's somewhere between 20k and 40k![/quote]
Reply
0
0
Shrikant
Shrikant     Abel Solomon
This comment was minimized by the moderator on the site
Facing same issue. Code works for sheets where data is less rows, however for larger data it shows error as "Excel cannot complete this task with available resources. Choose less data or close other applications" (there is no any other application working at same time) The code worked like a charm for a smaller data (less than 1200 rows). I tried to use on the bigger worksheet with (17000 rows) and it just crashed after splitting into 10-12 sheets. So we tried to split the original data into 3 different workbooks and still shut us down. We have Windows 7 and our computers are not that slow either. Do you suggest any limited rows on data to use this code safely? Any suggestion would be appreciated.
Reply
0
0
Bekki
Bekki
This comment was minimized by the moderator on the site
You are my all time hero! I have hunted for this for months with no luck. I have to do this weekly/monthly breaking reports into 147+ worksheets and they wont get me kutools. On that note.. I really need to learn coding. :( But thank you!
Reply
0
0
ACE
ACE
This comment was minimized by the moderator on the site
HI , I have a sheet having 65000 Records and having 8- different cases, so basically it should generate 80 different sheets. I tried running this code, but it throws Runtime Error 6 Overflow. Can this code be tweaked to solve my problem? Please ur help will be highly appreciated.
Reply
0
0
JD
JD     ACE
This comment was minimized by the moderator on the site
[quote]HI , I have a sheet having 65000 Records and having 8- different cases, so basically it should generate 80 different sheets. I tried running this code, but it throws Runtime Error 6 Overflow. Can this code be tweaked to solve my problem? Please ur help will be highly appreciated.By ACE[/quote] Try changing Dim vcol, i As Integer to Dim vcol, i As Long
Reply
1
0
Mustafa
Mustafa     JD
This comment was minimized by the moderator on the site
Hi I tried changing DIM vcol to LOng and it was working good but suddenly it through an error there isn't enough memory to complete this action try using less data or closing other applications. Though I don't have any other applications open. I'm having little more than 100 k rows and approx. 16 mb file size. any help would be appreciate. Thanks Mustafa
Reply
0
0
Croftycooke
Croftycooke
This comment was minimized by the moderator on the site
Awesome bit of code - works perfectly (if you change the variables to the one your spreadsheet needs)
Reply
0
0
Ahmed Sarheed
Ahmed Sarheed
This comment was minimized by the moderator on the site
I have a sheet with a variable number of rows. One of the columns is the dates ranging from 2010 onwards. The other columns are fund names with the NAV data for each fund against the date. So I do not want to split the columns into different sheets, I want to split each FUND NAME into its own sheet with the NAV data per End Of Month date, not the daily dates. Can this be done or is this impossible?
Reply
0
0
Tim
Tim
This comment was minimized by the moderator on the site
I have a worksheet that i use and i'm trying to find a vba code that will reconize a account name and copy the paticular row to a new workbook and sheet with the same name can you help?
Reply
0
0
Starscor
Starscor
This comment was minimized by the moderator on the site
Great! The VBA code is working, thanks! I need these output worksheets to be in individual excel files instead of worksheets and there is error when i split to many many worksheets.
Reply
0
0
kokoliso
kokoliso
This comment was minimized by the moderator on the site
Starscor and Tim if you want to split the sheets of the file into several files using the names of the rows there is a small macro code in this same web page that does it, just search for "split a workbook to separate Excel files" you will find it. Add the code of that example to the end of this one deleting the duplicate end sub and sub of course and you will get one file for each.
Reply
0
0
yash
yash
This comment was minimized by the moderator on the site
can anyone help me how to sort the columns in different sheets in the same workbook at a time and also to remove duplicates in different sheets as I have around 65 sheets in the same workbook
Reply
0
0
Theresa
Theresa
This comment was minimized by the moderator on the site
this is so exciting! Thank you. I've been lookign for this for some time.
Reply
0
0
Frankie
Frankie
This comment was minimized by the moderator on the site
Excellent - thank you for sharing this. Even propagates highlights/format to new worksheets!
Reply
0
0
There are no comments posted here yet
Load More

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL