팁 : 다른 언어는 Google 번역입니다. 방문하실 수 있습니다. English 이 링크의 버전.
로그인
x
or
x
x
회원가입
x

or

Excel의 열을 기반으로 여러 워크 시트로 데이터를 분할하는 방법은 무엇입니까?

거대한 데이터 행이있는 워크 시트가 있고 이제는 데이터를 여러 워크 시트로 분할해야합니다. 이름 열 (다음 스크린 샷 참조)을 입력하고 이름을 임의로 입력합니다. 어쩌면 당신은 먼저 그들을 정렬 할 수 있습니다 다음 복사 및 다른 새로운 워크 시트에 하나씩 그들을 붙여 넣습니다. 하지만 이렇게하면 반복해서 복사하여 붙여 넣기 위해 인내심을 가져야합니다. 오늘은이 작업을 해결하기위한 몇 가지 빠른 트릭에 대해 이야기하겠습니다.

1 열로 데이터 분할

VBA 코드가있는 열을 기반으로 여러 워크 시트로 데이터 분할

Excel 용 Kutools로 열 기반의 여러 워크 시트로 데이터 분할


워크 시트의 특정 열 또는 행 수를 기반으로 데이터를 여러 워크 시트로 분할합니다.

특정 열 데이터 또는 행 수를 기반으로 대형 워크 시트를 여러 시트로 분할하려는 경우 Excel 용 Kutools's 데이터 분할 기능을 사용하면이 작업을 빠르고 쉽게 해결할 수 있습니다.

6 열로 데이터 분할

Excel 용 Kutools: 200 이상의 편리한 Excel 추가 기능으로 60 일 동안 아무런 제한없이 시도 할 수 있습니다. 지금 무료 평가판 다운로드 및 다운로드!


VBA 코드가있는 열을 기반으로 여러 워크 시트로 데이터 분할


열 값을 기반으로 데이터를 신속하고 자동으로 분리하려는 경우 다음 VBA 코드를 사용하는 것이 좋습니다. 다음과 같이하십시오 :

1. 누르고있어. ALT + F11 키를 눌러 응용 프로그램 용 Microsoft Visual Basic 창.

2. 딸깍 하는 소리 끼워 넣다 > 모듈다음 코드를 모듈 창에 붙여 넣습니다.

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. 그런 다음, F5 키를 눌러 코드를 실행하면 헤더 행을 선택하라는 메시지 상자가 나타납니다 (스크린 샷 참조).

7 열로 데이터 분할

4. 그런 다음 OK 버튼을 클릭하고 두 번째 프롬프트 상자에서 분할하려는 열 데이터를 선택하십시오. 스크린 샷을 참조하십시오.

8 열로 데이터 분할

5. 그런 다음 OK활성 워크 시트의 모든 데이터는 열 값에 의해 여러 워크 시트로 분할됩니다. 그리고 분할 워크 시트는 분할 셀 이름으로 명명됩니다. 스크린 샷보기 :

2 열로 데이터 분할

주의 사항: 분할 워크 시트는 마스터 워크 시트가있는 통합 문서의 끝에 배치됩니다.


Excel 용 Kutools로 열 기반의 여러 워크 시트로 데이터 분할

엑셀 초급자로서,이 긴 VBA 코드는 다소 어렵습니다. 우리 대부분은 코드를 우리의 필요에 맞게 수정하는 방법조차 모르고 있습니다.

여기에서는 다기능 도구를 소개하겠습니다.Excel 용 Kutools데이터 분할 유틸리티는 열을 기반으로 여러 워크 시트로 데이터를 분할하는 데 도움이 될뿐만 아니라 행 수로 데이터를 분할 할 수도 있습니다.

Excel 용 Kutools : 300 이상의 편리한 Excel 추가 기능으로 60 일 동안 아무런 제한없이 시도 할 수 있습니다..

설치 한 경우 Excel 용 Kutools, 다음과 같이하십시오 :

1. 분할 할 데이터 범위를 선택하십시오.

2. 딸깍 하는 소리 Kutools 기타 > 워크 시트 > 데이터 분할, 스크린 샷보기 :

3 열로 데이터 분할

3. 에서 여러 워크 시트로 데이터 분할 대화 상자에서 다음을 수행해야합니다.

1). 고르다 특정 항목 에서 옵션 분할 기준 섹션을 선택하고 드롭 다운 목록을 기반으로 데이터를 분할 할 열 값을 선택하십시오. (데이터에 헤더가 있고 각 새 분할 워크 시트에 삽입하려는 경우 확인하십시오. 내 데이터에 헤더가 있습니다. 선택권.)

2). 그런 다음 분할 워크 시트 이름을 지정할 수 있습니다. 새 워크 시트 이름 섹션에서 워크 시트 이름 규칙을 지정하십시오. 규칙 드롭 다운 목록에서 접두사 or 접미사 시트 이름도 마찬가지입니다.

3). 클릭 OK 단추. 스크린 샷보기 :

4 열로 데이터 분할

4. 이제 데이터가 새 통합 문서의 여러 워크 시트로 분할됩니다.

5 열로 데이터 분할

Click for Kutools for Excel 및 무료 평가판을 다운로드하십시오!


Excel 용 Kutools로 열 기반의 여러 워크 시트로 데이터 분할

Excel 용 Kutools 300 이상의 편리한 Excel 도구가 포함되어 있습니다. 30 일 동안 아무런 제한없이 시도해보십시오. 무료 평가판을 지금 다운로드하십시오!


관련 기사 :

행을 기준으로 여러 워크 시트로 데이터를 분할하는 방법은 무엇입니까?


Excel 용 Kutools는 대부분의 문제를 해결하고 생산성을 80 % 증가시킵니다.

  • 재사용: 빠르게 삽입 복잡한 수식, 차트 그리고 당신이 전에 사용했던 것; 셀 암호화 비밀번호로; 메일 링리스트 만들기 그리고 이메일을 보내 ...
  • 슈퍼 포뮬러 바 (여러 줄의 텍스트와 수식을 쉽게 편집); 레이아웃 읽기 (많은 셀을 쉽게 읽고 편집); 필터링 된 범위에 붙여 넣기...
  • 셀 / 행 / 열 병합 데이터 손실없이; 분할 셀 내용; 중복 행 / 열 결합... 중복 세포 방지; 범위 비교...
  • 복제 또는 고유를 선택하십시오. 행; 빈 행 선택 (모든 세포는 비어있다); 슈퍼 찾기 및 퍼지 찾기 많은 통합 문서에서; 랜덤 선택 ...
  • 정확한 사본 공식 참조를 변경하지 않는 다중 셀; 참조 자동 작성 여러 장에; 글 머리 기호 삽입, 확인란 등 ...
  • 텍스트 추출, 텍스트 추가, 위치 별 제거, 공간 제거; 페이징 소계 생성 및 인쇄; 셀 내용과 주석 간 변환...
  • 수퍼 필터 (필터 구성표를 저장하고 다른 시트에 적용); 고급 정렬 월 / 주 / 일별, 빈도 등; 특수 필터 대담하고 기울임 꼴로
  • 통합 문서와 WorkSheets 결합; 키 열을 기준으로 테이블 병합 데이터를 여러 시트로 분할; 일괄 변환 xls, xlsx 및 PDF...
  • 300 이상의 강력한 기능. Office / Excel 2007-2019 및 365를 지원합니다. 모든 언어를 지원합니다. 기업이나 조직에 쉽게 배포 할 수 있습니다. 30 일 무료 평가판 전체 기능.
kte 탭 201905

Office 탭 Office에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게

  • Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, 게시자, 액세스, Visio 및 프로젝트.
  • 새 창보다는 동일한 창에서 새 탭으로 여러 문서를 열고 만들 수 있습니다.
  • 50 %만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄입니다!
오피셜
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.
    sha · 9 days 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 · 1 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 · 2 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 · 1 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 · 3 months 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 · 3 months 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 · 3 months ago
        Works great!


        Thanks for all your help!
  • To post as a guest, your comment is unpublished.
    Bernhard · 4 months ago
    Thank you, worked excellently!
  • To post as a guest, your comment is unpublished.
    Degardt · 4 months 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 · 4 months 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 · 3 months 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 · 5 months 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 · 5 months ago
    how do i make this work on around 150k rows count. Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 5 months 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.
    Aditya Bhatnagar · 7 months 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 · 4 months 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 · 6 months 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 · 8 months 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 · 8 months 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 · 8 months 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 · 8 months 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 · 10 months 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 · 10 months 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.
    avinash · 10 months 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 · 10 months 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 · 10 months 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 · 11 months 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 · 11 months 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 · 11 months 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.
    pradeep sharma · 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.
      parabola hiperbola · 9 months 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 · 9 months 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.
    Joshua · 1 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 · 1 years ago
    Thank you!!!
  • To post as a guest, your comment is unpublished.
    Bernarda · 1 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 · 1 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 · 1 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.
    Andreas · 1 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 · 1 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.
      Andreas · 1 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 · 1 years ago
    thank you so much
  • To post as a guest, your comment is unpublished.
    Lisl · 1 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 · 1 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 · 1 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 · 1 years ago
    This worked perfectly. Thank you!!!
  • To post as a guest, your comment is unpublished.
    rameez · 1 years ago
    Does the job, but does not retains the cell formats from the master worksheet
  • To post as a guest, your comment is unpublished.
    Kelly · 1 years ago
    Your data cannot be filtered. You can't even have filtering turned on. For the line

    title = "A1:C1"

    Enter your entire range. For instance mine was A1:N333

    I also named my range, but I don't know if that had any impact.
  • To post as a guest, your comment is unpublished.
    Loren · 1 years ago
    I realized this post is old but, I tried the code in Excel 2016. Couldn't get it to work. Does it need to be updated?
  • To post as a guest, your comment is unpublished.
    Jack · 1 years ago
    This code pastes the entries starting in A1 of new tabs. Is it possible to offset it? eg. move A1 to B7
  • To post as a guest, your comment is unpublished.
    Leo · 1 years ago
    I used your VBA code and it works great! - thank you sharing. : )
  • To post as a guest, your comment is unpublished.
    Roy · 1 years ago
    The code that Split Data Into Multiple Worksheets Based On Column With VBA Code above works very well. I need a small enhancement. How do you then check if the column value is empty and then create a worksheet called "BLANK" and copy all records with blank column value?
    • To post as a guest, your comment is unpublished.
      Phatmandrake · 1 years ago
      Without modifying the code you Could label all white spaces in your sorting column "BLANK".
  • To post as a guest, your comment is unpublished.
    Greg · 1 years ago
    Hi, this code is almost perfect. It creates the unique tabs and names them, however it copies all of the original information and pastes it into each new sheet. Not just the unique info. What am I doing wrong?
  • To post as a guest, your comment is unpublished.
    sonali belkar · 1 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 · 1 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 · 1 years ago
    Is there a ms access version of this? I'd like to do the same thing when exporting a query to excel