Kutools for Excel 22.00 HOT

300+ Powerful Features You Must Have in Excel

Kutools-for-Excel

Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!

Read More Download Buy now

Office Tab 14.00HOT

Adding Tabbed Interface for Office

Office Tab

It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo

Read More Download Buy now

Kutools for Outlook 13.00NEW

100+ Powerful Features for Outlook

Kutools-for-Outlook

Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!

Read More Download Buy now

Kutools for Word  9.00NEW

100+ Powerful Features for Word

Kutools-for-Word

Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!

Read More Download Buy now

Classic Menu for Office

Bringing Back Your Familiar Menus

Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.

Read More Download Buy now

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
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
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").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-2019 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
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ahmed Sarheed · 6 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Croftycooke · 6 years ago
    Awesome bit of code - works perfectly (if you change the variables to the one your spreadsheet needs)
  • To post as a guest, your comment is unpublished.
    ACE · 6 years ago
    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.
    • To post as a guest, your comment is unpublished.
      JD · 6 years ago
      [quote name="ACE"]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.[/quote]

      Try changing Dim vcol, i As Integer to Dim vcol, i As Long
      • To post as a guest, your comment is unpublished.
        Mustafa · 4 years ago
        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
  • To post as a guest, your comment is unpublished.
    Bekki · 6 years ago
    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!
  • To post as a guest, your comment is unpublished.
    MieMie · 6 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Abel Solomon · 4 years ago
      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]
      • To post as a guest, your comment is unpublished.
        Shrikant · 3 years ago
        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.
  • To post as a guest, your comment is unpublished.
    Gopinath · 6 years ago
    Amazing. Thanks for posting.
  • To post as a guest, your comment is unpublished.
    Aditi · 6 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Jonathan · 6 years ago
    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?
    • To post as a guest, your comment is unpublished.
      Ben · 4 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Bobby M · 6 years ago
    Works like a charm...

    Thank you for premium code... :lol:
  • To post as a guest, your comment is unpublished.
    Belinda Martinez · 6 years ago
    Works like a charm! Thank you.
  • To post as a guest, your comment is unpublished.
    Vickey · 6 years ago
    The VBA code worked perfectly. It does not seem to update the sheets as changes are made to Sheet1. Please assist.
    • To post as a guest, your comment is unpublished.
      Pieter · 6 years ago
      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
  • To post as a guest, your comment is unpublished.
    Niki · 6 years ago
    The VBA process worked perfectly, thank you so much for sharing your expertise and saving me a lot of time!
  • To post as a guest, your comment is unpublished.
    Cindy · 6 years ago
    I get an error when pressing F5 - GoTo Box asking for a reference??
  • To post as a guest, your comment is unpublished.
    Jagadesh.K · 6 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Lok · 3 years ago
      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
    • To post as a guest, your comment is unpublished.
      Lok · 3 years ago
      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
    • To post as a guest, your comment is unpublished.
      Sarah · 3 years ago
      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
    • To post as a guest, your comment is unpublished.
      Heidi · 3 years ago
      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!
    • To post as a guest, your comment is unpublished.
      Georges · 4 years ago
      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

Feature Tutorials