Log in
x
or
x
x
Register
x

or

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 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.

doc split data by columns 6

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!


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()
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.

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:

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.
    sonali belkar · 2 years ago
    it works very nice but i want to split data into multiple excel sheets based on column in excel.

    if anybody knows plz help.
  • To post as a guest, your comment is unpublished.
    Ashish · 2 years ago
    this code does update all splited data but I want to update only which cell would be updated cells.
  • To post as a guest, your comment is unpublished.
    Jason · 2 years ago
    Is there a ms access version of this? I'd like to do the same thing when exporting a query to excel
  • To post as a guest, your comment is unpublished.
    Mario · 2 years ago
    I use this VBA code to split commission statement. Can a SUM code be added to get a total on column C?
  • To post as a guest, your comment is unpublished.
    Mario · 2 years ago
    Hi, I use this VBA code all the time to separate commission statements. How can I add to sum column C at the end of each tab it generates?
  • To post as a guest, your comment is unpublished.
    Kristin P. · 2 years ago
    It works, but gets rid of the original list. I want a full inventory list, then be able to click a drop down and have the row stay on the main inventory and get copied over to a new sublist on a different sheet.
  • To post as a guest, your comment is unpublished.
    ram · 2 years ago
    it splitting whole data to every sheet please tell me how to solve this.
  • To post as a guest, your comment is unpublished.
    Laura · 2 years ago
    Hi...My titles are sometimes more than 31 letters long; how do I get around this?
    • To post as a guest, your comment is unpublished.
      Stan · 2 years ago
      Change the titles. That's really all you can do, honestly. I've run into this multiple times and you're not going to be able to exceed the character limit. Or create a helper column right next to the one you're looking to do the split based off of with the formula =LEFT(TITLE CELL, 30).
  • To post as a guest, your comment is unpublished.
    Daniel Woods · 2 years ago
    Perfect solution, saves me a job - thank you!
  • To post as a guest, your comment is unpublished.
    Tracy Hinds · 2 years ago
    Thank you! Split data with VBA is an excellent time saver! The only issue that I had is when data in vcol is not a valid tab name. Is there a way to truncate the name to something allowed or populate the data even if the sheet name is "Sheet" #? It created blank sheets named "Sheet" # instead of putting the data on the sheet.
  • To post as a guest, your comment is unpublished.
    Siddharth · 2 years ago
    Hi
    I have split the data using VBA and the result was good except that I am getting 2 blank sheet.
    For Eg. Total sheet as per filter needed 70 sheet but got only 68sheets and 2 sheets were blank.

    Can someone help me with this ??
  • To post as a guest, your comment is unpublished.
    Bitterbug · 2 years ago
    When I use this to split the data, I lose all formulas including conditional formatting. I also want to include subtotal in the last column on all sheets. How to do this?
  • To post as a guest, your comment is unpublished.
    Nick M · 2 years ago
    Post #4 on this page does what this should do and doesn't crash my computer like this did!

    https://www.mrexcel.com/forum/excel-questions/727407-vba-split-data-into-multiple-worksheets-based-column.html
  • To post as a guest, your comment is unpublished.
    Carl · 2 years ago
    Hi, this worked fine once and then i get runtime error '9' subscript out of range at line Set ws = Sheets("[NAME]").


    Why would this be happening?
    • To post as a guest, your comment is unpublished.
      Sharda · 2 years ago
      Hi Carl,
      Even I was getting this error, but
      Do not change ("Sheet1") to anything where script says as Set ws = Sheets("Sheet1")
      It worked for me, hope it works for you as well.
      Cheers
  • To post as a guest, your comment is unpublished.
    Adam Miller · 2 years ago
    The VBA successfully split the data into separate worksheets with most of the unique names from the column I set but it copied all of the data from the master sheet to all of the split out worksheets. I could have done that quicker myself with cut and paste or just filtering by the column in question. Not much of a solution and by the lack of responses below, it doesn't look like much will be done about it?
  • To post as a guest, your comment is unpublished.
    Courtney M · 2 years ago
    Using Kutools or Macros - how can I split and maintain a multi row header on all of the sheets?
  • To post as a guest, your comment is unpublished.
    Paras Gadhavi · 2 years ago
    Hi there,

    Thanks for sharing. This works for me partially. The code splits the data but only for one of the values in the column and creates blank sheets for others. Can you help me with this ?
  • To post as a guest, your comment is unpublished.
    cyndi · 2 years ago
    This code worked perfect with just three columns but when I tried to run it on a worksheet containing 14 columns and 21,767 rows I received and error message "Run time error 9, Subscript out of range". Any help would be appreciated
  • To post as a guest, your comment is unpublished.
    Ana · 2 years ago
    HI. it will give me an error "lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row"

    what did I do wrong?
  • To post as a guest, your comment is unpublished.
    Sandeep Jha · 2 years ago
    Hello I am new to VBA programming. Here I needed to filter rows based on 1st column data. So I used your VBA code but I don't know why the sheets that are created are created twice except for the last one. I can't figure out the error in this so can you please help me out?

    Thank You
  • To post as a guest, your comment is unpublished.
    Osman · 2 years ago
    Just wondering if anyone knows how to: If I split data, and the original data tab changes e.g. more actions are listed, how do I get the subsequent tabs to update?

    Thank you
  • To post as a guest, your comment is unpublished.
    Joyce T · 2 years ago
    OMG, you just saved me hours of time. Thanks!
  • To post as a guest, your comment is unpublished.
    Yee · 2 years ago
    can it split base on date? i tried but date become "text" on sheet and no data on the individual sheet. I try change the date to "text" it works
    Please assist
    thanks & regards
  • To post as a guest, your comment is unpublished.
    Sonu Saifi · 2 years ago
    I have a Question ,
    when i am using VBA Code i face a error 'Run time error 6'. so, please help me how to resolve this issue...

    Thanks in Advance...
    • To post as a guest, your comment is unpublished.
      Shiva · 2 years ago
      Did that solve? if yes, Please share the same.
  • To post as a guest, your comment is unpublished.
    Jana · 2 years ago
    I get error message "Compile error: Syntax error" and then arrow pointing at "Sub parse_data()" which is also highlighted in yellow.

    Any suggestions what could went wrong?
  • To post as a guest, your comment is unpublished.
    SIKANDAR · 2 years ago
    I get the error message "Run-time error '6': Overflow"
  • To post as a guest, your comment is unpublished.
    Dylan · 2 years ago
    Kutool vbscript (Developer- micros-input-module) splits sheets into many individual sheets based on the vcol choosen (eg J/11). If the specified vcol has text > 31 characters, the output is displays those sheets as blank sheet with no data.


    Q: where in the script, can one change the characters to be more than 31?



    note: Excel (Data) does allow one to use the text to columns( fix with) to insert a line at 31, to limit the characters to conform to vbscript. My wish is to change/ remove the character limitation.



    thank you


    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 = 10
    Set ws = Sheets("Sheet1")
    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
  • To post as a guest, your comment is unpublished.
    Dylan · 2 years ago
    im am running a vbscript to split and save xls workbook, but it omits long text(>30 characters).

    how can i change the characters to >30 in the script?
  • To post as a guest, your comment is unpublished.
    Kannan · 2 years ago
    Dear sir

    How create column data into separate workbook
  • To post as a guest, your comment is unpublished.
    imran · 2 years ago
    I am getting overflow error at below line
    For i = 2 To lr
    • To post as a guest, your comment is unpublished.
      Walid J · 2 years ago
      i got the same error, did you find a sollution to that?
      • To post as a guest, your comment is unpublished.
        ven · 2 years ago
        if you get an overflow error, change data type of I as long. may be it will work. worked for me.
  • To post as a guest, your comment is unpublished.
    Rajeshkumar · 2 years ago
    Hi,

    Thanks for great code!!!

    I have a requirement that,

    1. It shall maintain the master sheet formatting &
    2. When any changes is made in the master sheet, the values in the sub-sheets gets updated.

    Looking your support on this.

    Thanks a ton..

    Cheers,

    Rajesh
  • To post as a guest, your comment is unpublished.
    Rohit Kulkarni · 2 years ago
    Hi,

    How can I have it automatically update the main sheet when I make changes to the generated subsheet ?

    Thanks
    Rohit
  • To post as a guest, your comment is unpublished.
    Jeffrey Roth · 2 years ago
    So is there a way to preserve the formulas when using this code?
  • To post as a guest, your comment is unpublished.
    Marc T · 2 years ago
    The VBA is not working correctly for me. It is separating sheets but the data is not transferring, so I'm getting blank sheets. What am I missing?

    Thanks,
    • To post as a guest, your comment is unpublished.
      Jodye · 2 years ago
      My new sheets are being created with the correct titles, but the data within them is not filtered. Each new sheet contains all of the data from the master. My version of your VB code is using the correct vcol, it's just not filtering. I'm so close!
    • To post as a guest, your comment is unpublished.
      Jodye · 2 years ago
      Same with me. It generated the separate sheets with the correct titles, but the data in those sheets includes everything from the master, not filtered based on the value/title. It is definitely using the vcol value/column that I want, it's just not filtering. I'm so close!
    • To post as a guest, your comment is unpublished.
      Dipak · 2 years ago
      vcol =1, the number 1 is the column number that you want to split the data based on.
      Eg. Col.1 for A,. 2 for B, 3 For C and so on.

      Set ws = Sheets("Sheet1"), Sheet1 is the sheet name that you want to apply this code.
      You know this

      title = "A1:C1", A1:C1 is the range of the title.
      If u have columb from A to M then = "A1:M1", And u have to include 2 rows in separate sheet then type = "A1:M2",

      Keep in mind
      U just set filter to data. Don't filter any columb data and apply code.
      U just set filter to data and apply code
  • To post as a guest, your comment is unpublished.
    Tony · 2 years ago
    Great stuff! worked so well for many different files
  • To post as a guest, your comment is unpublished.
    Gemma · 2 years ago
    Hi, thanks for this, both the VBA code and Kurtools split data work well for me, however, do you know if I can copy this data into pre formatted work sheets rather than new work sheets with either options please?

    Thanks
  • To post as a guest, your comment is unpublished.
    Gemma · 2 years ago
    Hi, do you know if you can use this great VBA code or the Kutools split data option with pre formatted work sheets, rather than the data transferring into new work sheets? Thanks
  • To post as a guest, your comment is unpublished.
    Swaroop Pallem · 2 years ago
    hey help me to write a macro to split data in to two sets with in a excel
  • To post as a guest, your comment is unpublished.
    Cheri · 2 years ago
    Awesome!! THANK YOU!!
  • To post as a guest, your comment is unpublished.
    Sarah · 2 years ago
    HI,

    I am trying to split data as per dates. I have got 30000 cells and need to split them according to months in different worksheet.

    I've got 8 columns and date are in second coloum that is column B.

    Could you please help me with it.

    thanks in advance
  • To post as a guest, your comment is unpublished.
    KD · 2 years ago
    [quote name="Bobby M"]Works like a charm...

    Thank you for premium code... :lol:[/quote]


    I also need the premium key(Code)
  • To post as a guest, your comment is unpublished.
    Kate · 2 years ago
    This is amazeballs ! thanks - I have been asking how to do this with the whizz kids and its so complicated. Really pleased with it
  • To post as a guest, your comment is unpublished.
    suly · 2 years ago
    how to extract each letter of the date in sheet1 < sheet2 got some dates> and make it apear in sheet3 for example

    sheet1
    james 22.4.2017 23.4.2017 24.4.2017
    a r r

    tom 22.4.2017 23.4.2017 24.4.2017
    r r r


    sheet2

    james 22.4.2017

    james 23.4.2017

    tom 24.4.2017

    sheet3


    james 22.4.2017 a

    james 23.4.2017 r

    tom 24.4.2017 r





    and thank u
  • To post as a guest, your comment is unpublished.
    Chuck · 2 years ago
    This code will probably save me a few hours over the next few months! Thanks!!!

    Is there a way to then open all of those new tabs in separate windows? Or, is there a way to email a single tab to someone and not the entire thing?

    Thanks again!
  • To post as a guest, your comment is unpublished.
    Christopher · 2 years ago
    This code worked great! Very useful...

    On only had one error... One of the groupings that I split was titled "MAINTENANCE/OPERATIONS" - as we all know, Excel doesn't like to title sheet names with "/" in the title. The script you offered created a blank sheet (Sheet 24) is what it titled it, but it failed to copy the header and data from the original sheet. Not a big deal, I was able to copy and paste this single instance to the sheet and rename the sheet "MAINTENANCE-OPERATIONS" to comply with Excel's rules about naming sheets.

    Is there a way to revise the code so that when I run the module, it will automatically change bad characters in a sheet name to a default character like "_". I plan on using this code a lot and I am certain that the data sheets I will be running will have this issue.

    Thank you so much for any help!
  • To post as a guest, your comment is unpublished.
    RiggaToni · 2 years ago
    Thank you! Saved me a ton of time!
  • To post as a guest, your comment is unpublished.
    Shwetha · 2 years ago
    Thank you for the code. It works good. It saved me lot of time from recreating the whole thing.
  • To post as a guest, your comment is unpublished.
    Anshul Gupta · 2 years ago
    Hi, My files doesn't have titles, how can i tweak the code such that it won't contain first row as title
  • To post as a guest, your comment is unpublished.
    RC-S · 2 years ago
    You are quite frankly amazing. This macro saved my bacon in more ways than one :lol:
  • To post as a guest, your comment is unpublished.
    D C · 3 years ago
    VBA code is very good .It saves a lot of time thank your for this code thank you very much........................................................