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.
    Jaco van der Merwe · 1 months ago
    Hi there,
    Thank you so much for this post. It is really very helpful.

    I have another situation that requires me to split salary data per department so that each manager will receive a workbook for their team only. I do not mail these out to managers, I simply safe it in their respective folder on the server.

    I use this code, but this code does not split the worksheet into separate files. Can you please help me to modify this code so that it will create separate workbooks for me in the same directory as the master sheet?

    Sub SplitIntoSheets()
    With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    End With
    ThisWorkbook.Activate
    Sheet1.Activate
    'clearing filter if any
    On Error Resume Next
    Sheet1.ShowAllData
    On Error GoTo 0
    Dim lsrClm As Long
    Dim lstRow As Long
    'counting last used row
    lstRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim Planners As Range
    Dim clm As String, clmNo As Long
    On Error GoTo handler
    clm = Application.InputBox("From which column you want create files" & vbCrLf & "E.g. A,B,C,AB,ZA etc.")
    clmNo = Range(clm & "1").Column
    Set Planners = Range(clm & "2:" & clm & lstRow)
    'Calling Remove Duplicates to Get Unique Names
    Set Planners = RemoveDuplicates(Planners)
    Call CreateSheets(Planners, clmNo)
    With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .AlertBeforeOverwriting = True
    .Calculation = xlCalculationAutomatic
    End With
    Sheet1.Activate
    MsgBox "Well Done!"
    Exit Sub
    Data.ShowAllData
    handler:
    With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .AlertBeforeOverwriting = True
    .Calculation = xlCalculationAutomatic
    End With
    End Sub
    Function RemoveDuplicates(Planners As Range) As Range
    ThisWorkbook.Activate
    Sheets.Add
    On Error Resume Next
    ActiveSheet.Name = "Planners"
    Sheets("Planners").Activate
    On Error GoTo 0
    Planners.Copy
    Cells(2, 1).Activate
    ActiveCell.PasteSpecial xlPasteValues
    Range("A1").Value = "Planners"
    Dim lstRow As Long
    lstRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:A" & lstRow).Select
    ActiveSheet.Range(Selection.Address).RemoveDuplicates Columns:=1, Header:=xlNo
    lstRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set RemoveDuplicates = Range("A2:A" & lstRow)
    End Function
    Sub CreateSheets(Planners As Range, clmNo As Long)
    Dim lstClm As Long
    Dim lstRow As Long

    For Each Unique In Planners
    Sheet1.Activate
    lstRow = Cells(Rows.Count, 1).End(xlUp).Row
    lstClm = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim dataSet As Range
    Set dataSet = Range(Cells(1, 1), Cells(lstRow, lstClm))
    dataSet.AutoFilter field:=clmNo, Criteria1:=Unique.Value
    lstRow = Cells(Rows.Count, 1).End(xlUp).Row
    lstClm = Cells(1, Columns.Count).End(xlToLeft).Column
    Debug.Print lstRow; lstClm
    Set dataSet = Range(Cells(1, 1), Cells(lstRow, lstClm))
    dataSet.Copy
    Sheets.Add
    ActiveSheet.Name = Unique.Value2
    ActiveCell.PasteSpecial xlPasteAll
    Next Unique
    End Sub
    • To post as a guest, your comment is unpublished.
      skyyang · 1 months ago
      Hello, Jaco,
      To split the data into multiple separate workbooks based on the column value, the code in the following article may help you:
      https://www.datanumen.com/blogs/2-fast-means-to-split-an-excel-worksheets-contents-into-multiple-workbooks-based-on-a-specific-column/

      Please try it, thank you!
  • To post as a guest, your comment is unpublished.
    Ani · 1 months ago
    Hi, it is working great but I need to keep the header row and the infromation above it in each sheet and also the formatting of the header row.
    Does anyone know how this can happen?


  • To post as a guest, your comment is unpublished.
    Joe · 2 months ago
    This is awesome!! Thank you so much for sharing!
  • To post as a guest, your comment is unpublished.
    Kevin · 3 months ago
    Hey, i really need some help.
    1. first i want to to split based on value and after that to split per 1k based on value before.
    2. After split per 1k, i need to save as those files to xls.

    Can someone help me to solve thats problem
  • To post as a guest, your comment is unpublished.
    nk · 4 months ago
    This macro has come in handy more times than i can count, Does anyone have a work around for these two issues? Any help is appreciated!

    1. make the Macro work form a worksheet that is formatted as a table and maintain the same formatting in the new worksheets? I have been able to use this macro when the worksheet is not formatted.

    2. split columns that Include blank cells from the original worksheet to the new worksheets. My original data has some empty cells, that need to remain empty, when i use this macro, the divided columns exclude the data that had an empty cell in the column that was split.
  • To post as a guest, your comment is unpublished.
    NK · 4 months ago
    This macro has come in handy more times than i can count, Does anyone have a work around for these two issues? Any help is appreciated!

    1. make the Macro work form a worksheet that is formatted as a table and maintain the same formatting in the new worksheets? I have been able to use this macro when the worksheet is not formatted.

    2. split columns that Include blank cells from the original worksheet to the new worksheets. My original data has some empty cells, that need to remain empty, when i use this macro, the divided columns exclude the data that had an empty cell in the column that was split.
  • To post as a guest, your comment is unpublished.
    AshSam123 · 5 months ago
    Hey everyone. I am completely new to VBA. I have an account receivable data in an excel sheet and there are multiple column. One column mention the invoice no and the other mentions the department responsible for that invoice. I have tried this code, it split the column by department into multiple sheets but it does not include the invoice no. I have a big list of data and I don't to want copy and paste the data, can someone please help me. Thanks
    • To post as a guest, your comment is unpublished.
      AshSam123 · 5 months ago
      Thank you for your response. I cannot post the original, but here is the sample data. I hope you can help me. Many Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 5 months ago
      Hi, Ayesha,
      Could you upload your problem screenshot here? Thank you!
  • To post as a guest, your comment is unpublished.
    Guest25 · 6 months ago
    The vba code is not working if the column values to split on has a length of 26. If I manually change it to 12 it works. Is there a reason for it. I understand that excel worksheet name cannot exceed 30 characters but here the length is 26 but it does not work. Can you please help me understand this. Thanks.
    • To post as a guest, your comment is unpublished.
      skyyang · 5 months ago
      Hello,
      Thank you for your comment, yes, as you said, the above vba code is not worked correctlly for cell values which text length are much longer, but, here, you can apply our product, Kutools for Excel, with its Split Data feature, you can achieve this job quickly and easily. You can download and free trail 30 days. Please try, thank you!
  • To post as a guest, your comment is unpublished.
    Melvin · 6 months ago
    the code to split the data works perfectly, except it skips some data and renames these as "sheet#"
  • To post as a guest, your comment is unpublished.
    sha · 9 months ago
    thank you very much, it helped me a lot in my work. However, is there a way to have the macro create a separate spreadsheet for each new tab instead of just adding a tab into the current worksheet? Thanks!
  • To post as a guest, your comment is unpublished.
    G · 11 months ago
    How can I make it to automatically choose a smaller name for the sheet instead of skipping an entry and returning sheet 4, sheet 5 etc.

    E.G: I am using this amazing code to dissect my excel sheet, but some entries have a long name which exceeds the sheet name count, instead of using all 31 symbols that are allowed, it cancels the filtering and returns sheet 4 instead.
  • To post as a guest, your comment is unpublished.
    K · 11 months ago
    Could I make it so that I would be able to automatically split it so both Emily and Lucy's are in one page, and Jone and Steven's are in another?
    • To post as a guest, your comment is unpublished.
      skyyang · 11 months ago
      Hi,
      Sorry, at present, maybe there is not a good method for solving your problem. But you can combine the two sheets you need after splitting.
  • To post as a guest, your comment is unpublished.
    Mark · 1 years ago
    This works great but is there a way I can carry over my formulas from the original worksheet?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Mark,
      The above VBA code helps to keep the formula cells after splitting the data, please try!
      Thank you!
      • To post as a guest, your comment is unpublished.
        Mark · 1 years ago
        Works great!


        Thanks for all your help!
  • To post as a guest, your comment is unpublished.
    Bernhard · 1 years ago
    Thank you, worked excellently!
  • To post as a guest, your comment is unpublished.
    Degardt · 1 years ago
    This code is brilliant. It does exactly what I need. Only, I don't know how to change the code so that the data in the rows, all starting with the same letter, should go on the same worksheet, instead of each getting its own worksheet. I have 1000 rows of data, some starting with "L", or "K"or "F" and then a number. It sorts the data each to its own sheet, I want all the "L" cells' data on one sheet and all the"K" data on one sheet. Can someone please help me. I don't underwstand coding that good
    • To post as a guest, your comment is unpublished.
      MP · 1 years ago
      Add a column and pull the first character from the cell with L+ the number or K+ the number. Assume your key is in cell A2, use the formula =left(A2,1) to pull the first character into your new column. Use this column to separate data to its own sheet.
      • To post as a guest, your comment is unpublished.
        Degardt · 1 years ago
        Thank you MP, I will definitely try that. I'm new to coding and still trying to figure it out as I go. But it's crunch time and i need the program to work ASAP. For now I'm just struggling a bit. Lol
  • To post as a guest, your comment is unpublished.
    Aaron · 1 years ago
    Why does the VBA create new sheets with columns all the way to XFD when my main sheet only goes to AK?
  • To post as a guest, your comment is unpublished.
    ShiroKuro · 1 years ago
    how do i make this work on around 150k rows count. Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, ShiroKuro,
      If there are large data in your worksheet, I recommend Kutools for Excel's Split Data feature for you, you can download it and free trial 60-day!
      Please try, thank you!
  • To post as a guest, your comment is unpublished.
    adityabhatnagar2103@gmail.com · 1 years ago
    Great script! Could someone please help as I need to just add "Class-C" at the end of each new worksheet's name that is created after running this. For Eg. Lucy-Class C; Emily Class C; and so on. Would really appreciate your help here.
    • To post as a guest, your comment is unpublished.
      Me · 1 years ago
      just add a column and concatenate the Name and the "-Class C" field and use that as the column to split on instead of the Name column, then you can hide the column if you want
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Aditya,
      Here, I recommend Kutools for Excel's Split Data feature for you, you can add the prefix or suffix text easily in the dialog box as you need.
      You can download the tool and free trial 60-day.
      Please try, thank you!
  • To post as a guest, your comment is unpublished.
    Katharina · 1 years ago
    Great Script, thanks! What do I have to do to set the header row range and column to use for grouping/splitting within the script? I know it is more elegant to use selectable parameters but for my use case it is always the same. As I do know nothing about VBA, any little hint is appreciated :-) Best, Katharina
  • To post as a guest, your comment is unpublished.
    daiana · 1 years ago
    It doesn't work with 120000 rows. Is there any way to make it work?
  • To post as a guest, your comment is unpublished.
    Radoslav · 1 years ago
    Perfect!!! Works and refer to all my demands. Tnx for that source code.
  • To post as a guest, your comment is unpublished.
    Rudi Miller · 1 years ago
    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("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.
    Jason · 1 years ago
    This formula is great, works perfectly for me.
    I want to split out data based on a location, which is in column 1. Which this does.
    However, is it possible to also split out based on column 2, for example. Built and Not Built. So a secondary condition also?
  • To post as a guest, your comment is unpublished.
    jose · 1 years ago
    can someone help please im using this but i keep getting to many columns. i have to keep deleting rows every time i use this.

    This is what im using


    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("sheet1")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1:AN1"
    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.
    itsavinash · 1 years ago
    Thanks for VBA, it works great. In addition to that question, I have column which is dependent. So when i select some value my column values get changed hence I need vba solution to replace earlier split worksheet to replace with new value of columns. Can anyone help me out.?

    Thanks in advance
  • To post as a guest, your comment is unpublished.
    JP Tontegode · 1 years ago
    Is there a way to have the macro create a separate spreadsheet for each new tab instead of just adding a tab into the current worksheet? Thanks!
  • To post as a guest, your comment is unpublished.
    Sebastian · 1 years ago
    Effectively, this code does work great. I wrote an additional code so that I could get subtotal on certain columns but it has not worked great. So I tried running the subtotals from the master list, but afterwards when I use this macro it create a whole new tab for the Grand total row. So I am getting two two tabs per split. the first one is fine because it splits with the grand totals, but then creates a second one with just the grand total row. Any help as to how to modify this.
    This is the code that I am currently using:
    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 = 3
    Set ws = Sheets("Master sheet")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1:R1"
    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.
    Jenny Singleton · 1 years ago
    Hi, This VBA worked great on my first attempt. However, when repeating the process with a new set of data, the sheets are splitting into Product names, but the data in each sheet still shows all data. I can't understand why it worked first time though. Can anyone help please. Thanks
  • To post as a guest, your comment is unpublished.
    Trisha · 1 years ago
    This code is brilliant - Thank you. Just one thing - its splitting the data as I need it too but amongst creating the relevant sheets its creating blank sheets too.


    Are you able to help with this?
  • To post as a guest, your comment is unpublished.
    aaseef.khan@gmail.com · 1 years ago
    Thanks for the Magic Words
  • To post as a guest, your comment is unpublished.
    Muhammad Jahanzaib · 1 years ago
    Thanks for excellent & brilliant code. I came across a bug, if field name is too long, the new sheet is not created. It should be like that the new sheet may be named with the maximum number of characters allowed. Regards,
  • To post as a guest, your comment is unpublished.
    Alex S · 1 years ago
    To anyone having issues with long sheet names (ie Sheet Names greater than 30 characters), replace:

    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

    with

    Dim sheetName As String
    For i = 2 To UBound(myarr)
    ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
    ' Replace the below assignment to sheetName as you wish
    sheetName = Left(CStr(i - 1) & "_" & myarr(i), 30)
    If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
    Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = sheetName
    Else
    Sheets(sheetName).Move after:=Worksheets(Worksheets.Count)
    End If
    ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(sheetName).Range("A1")
    Sheets(sheetName).Columns.AutoFit
    Next

    This essentially limits the Sheet Name to legal limits of 30 characters. If you do not like the way the sheet is named, replace sheetName's assignment whatever you would like, keeping in mind that no two sheets can have the exact same name and must also be 30 characters or less.

    You can also remove the filtering that lingers at the end of the execution by adding this line just before "End Sub"

    On Error Resume Next
    ActiveSheet.ShowAllData

    Enjoy ^_^
  • To post as a guest, your comment is unpublished.
    prdpsharma90@gmail.com · 1 years ago
    I 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.
    Pradeep Sharma · 1 years ago
    The 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.
    Jorge Portillo · 1 years ago
    The 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.
    Manish · 1 years ago
    I 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.
      hipppar@gmail.com · 1 years ago
      hi. I have similar situation, I want to keep first 8 rows in every sheet created. did you find any solution to this?
      • To post as a guest, your comment is unpublished.
        skyyang · 1 years ago
        Hi, guys,
        If your worksheet data contains multiple header rows, the below VBA code can solve your prolem, please try it.

        Sub Parse_data_0213()
        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

        Hope it can help you, thank you!
  • To post as a guest, your comment is unpublished.
    Dilusha · 1 years ago
    How can I get the Total of Column C for each sheets.?
  • To post as a guest, your comment is unpublished.
    Samurai · 2 years ago
    This is making new, labeled sheets for everything from my selected column, but it's not actually moving any of the associated data into the new sheets. What am I missing?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Joshua,
      When applying the above code, you should change the references to your need, and this code is not applied correctly if your key column is date column, we will update a new code later.
  • To post as a guest, your comment is unpublished.
    Angie · 2 years ago
    Thank you!!!
  • To post as a guest, your comment is unpublished.
    Bernarda · 2 years ago
    Hola, me sirvio el formato pero querría que lo que se copie en cada solapa nueva lleve también la formula de la base. Cómo podría hacer?
  • To post as a guest, your comment is unpublished.
    snow-raven · 2 years ago
    Easiest bit of code stealing I've ever done. Thank you for sharing this incredible tool!
  • To post as a guest, your comment is unpublished.
    Kriti · 2 years ago
    I have my tittle from A1 to X3, I updated title in code but still getting only one row in title.
  • To post as a guest, your comment is unpublished.
    AnDM · 2 years ago
    Works perfectly, thanks :)
    Is there maybe a possibility to apply this to previously filtered data only as well? I´m having a hard time figuring out the VBA code on my own.
    I appreciate any help :)
  • To post as a guest, your comment is unpublished.
    phillipa hatt · 2 years ago
    I can not sort this data at all, i've used this quite a few times in the past but it doesn't seem to be working anymore, it is bringing up new sheets up but they blank. please help!!!
    • To post as a guest, your comment is unpublished.
      AnDM · 2 years ago
      How long are your titles? Because some of mine were longer than 31 characters and returned empty sheets as well.
  • To post as a guest, your comment is unpublished.
    Jiji · 2 years ago
    thank you so much
  • To post as a guest, your comment is unpublished.
    Lisl · 2 years ago
    Hi there I ran the parse_data macro

    But it has a run-time error '6': overflow debug gets stuck on For i = 2 To lr

    Is there a size limit, i have about 500 000 line items?

    Please can you help?
    • To post as a guest, your comment is unpublished.
      Mohamed · 2 years ago
      A Tip :
      if you have a large data to copy paste, memory limit can be reached (error 6 ).
      Forcing file saving within the loop clears the memory . in the For i = 2 To UBound(myarr) add before Next :
      ThisWorkbook.Save
      DoEvents
  • To post as a guest, your comment is unpublished.
    Chuka · 2 years ago
    Thank u so much. But what if split the data and transpose values. Is there anyone help me pls
  • To post as a guest, your comment is unpublished.
    David · 2 years ago
    This worked perfectly. Thank you!!!
  • To post as a guest, your comment is unpublished.
    rameez · 2 years ago
    Does the job, but does not retains the cell formats from the master worksheet