ヒント:他の言語はGoogle翻訳されています。 訪問することができます English このリンクのバージョン。
ログイン
x
or
x
x
LPI ID 登録
x

or

Excelの列に基づいて複数のワークシートにデータを分割する方法は?

巨大なデータ行を持つワークシートがあるとしたら、データを複数のワークシートに分割する必要があります。 氏名 列(次のスクリーンショットを参照)、名前はランダムに入力されます。 たぶんあなたは最初にそれらを並べ替えることができますし、それらをコピーし、別の新しいワークシートに1つずつ貼り付けます。 しかし、これは繰り返しコピーして貼り付けるためにあなたの忍耐を必要とします。 今日、私はこの作業を解決するための簡単なテクニックを話します。

1列によるデータの分割

VBAコードの列に基づいて複数のワークシートにデータを分割する

Kutools for Excelで列に基づいて複数のワークシートにデータを分割する


ワークシートの特定の列または行数に基づいてデータを複数のワークシートに分割する:

大きなワークシートを特定の列データまたは行数に基づいて複数のシートに分割する場合は、 Kutools for Excel's 分割データ このタスクを迅速かつ簡単に解決するのに役立ちます。

6列によるデータの分割

Kutools for Excel200便利なExcelアドイン以上で、60日に制限なく試してみることができます。 今すぐダウンロードして無料トライアル!


VBAコードの列に基づいて複数のワークシートにデータを分割する


列の値に基づいてデータをすばやく自動的に分割する場合は、次のVBAコードを選択することをお勧めします。 このようにしてください:

1。 押さえつける Alt + F11 キーを押して アプリケーション用Microsoft Visual Basic 窓。

2に設定します。 OK をクリックします。 インセット > モジュール次のコードをモジュールウィンドウに貼り付けます。

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 ボタンをクリックし、2番目のプロンプトボックスで、分割する列データを選択します。スクリーンショットを参照してください。

8列によるデータの分割

5。 次に、をクリックします。 OKアクティブなワークシートのすべてのデータは、列の値によって複数のワークシートに分割されます。 また、分割ワークシートには分割セル名が付けられています。 スクリーンショットを参照してください。

2列によるデータの分割

:分割ワークシートは、マスターワークシートがあるワークブックの最後に配置されます。


Kutools for Excelで列に基づいて複数のワークシートにデータを分割する

Excelの初心者として、この長いVBAコードは私たちにとってはやや難しく、ほとんどの人はコードを私たちの必要性として修正する方法を知らないことさえあります。

ここでは、多機能ツールを紹介します。Kutools for Excel、 その 分割データ ユーティリティは、列に基づいて複数のワークシートにデータを分割するのに役立つだけでなく、行数でデータを分割することもできます。

Kutools for Excel : 300便利なExcelアドイン以上で、60日以内に自由に試してみてください.

あなたがインストールしている場合 Kutools for Excel次のようにしてください:

1。 分割するデータの範囲を選択します。

2に設定します。 OK をクリックします。 Kutools Plus > ワークシート > 分割データ、スクリーンショットを見る:

3列によるデータの分割

3。 の中に データを複数のワークシートに分割する ダイアログボックスでは、次の操作が必要です。

1)。 選択 特定の列 内のオプション に基づいて分割 ドロップダウンリストからデータを分割する列の値を選択します。 (データにヘッダーがあり、新しい分割されたワークシートに挿入する場合は、チェックしてください 私のデータにヘッダーがあります オプション。)

2)。 次に、分割されたワークシート名を 新しいワークシート名 セクションで、ワークシート名のルールを キャンペーンのルール ドロップダウンリストでは、 接頭辞 or サフィックス シート名についても同様です。

3)。 クリック OK ボタン。 スクリーンショットを見る:

4列によるデータの分割

4。 これで、データは新しいワークブックの複数のワークシートに分割されます。

5列によるデータの分割

クリックしてExcel用のKutoolsをダウンロードし、今すぐ無料トライアル!


Kutools for Excelで列に基づいて複数のワークシートにデータを分割する

Kutools for Excel 300以上の便利なExcelツールが含まれています。 自由に30日に制限なしで試してみてください。 今すぐ無料トライアルをダウンロードしてください!


関連記事:

行数によって複数のワークシートにデータを分割する方法は?


Kutools for Excelは、ほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート そして、以前に使用したもの; セルを暗号化する パスワード付き メーリングリストの作成 そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストや数式を簡単に編集する) レイアウトを読む (多数のセルを簡単に読んで編集できます)。 フィルター範囲に貼り付ける...
  • セル/行/列を結合 データを失うことなく; セルコンテンツの分割。 重複する行/列を結合する...重複セルの防止。 範囲の比較...
  • 重複または一意を選択します空白行を選択 (すべてのセルは空です)。 スーパー検索とファジー検索 多くのワークブックで。 ランダム選択
  • 完全コピー 式の参照を変更せずに複数のセル。 参照を自動作成 複数のシートに 箇条書きを挿入、チェックボックスなど
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷 セルのコンテンツとコメント間の変換...
  • スーパーフィルター (保存して他のシートにフィルタ方式を適用する)。 高度な並べ替え 月/週/日、頻度などによる。 特殊フィルター 太字、斜体で...
  • ワークブックとワークシートを組み合わせる; キー列に基づいて表をマージします。 データを複数のシートに分割する; 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 · 1 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 · 2 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 · 3 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 · 2 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 · 4 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 · 4 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 · 4 months ago
        Works great!


        Thanks for all your help!
  • To post as a guest, your comment is unpublished.
    Bernhard · 5 months ago
    Thank you, worked excellently!
  • To post as a guest, your comment is unpublished.
    Degardt · 5 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 · 5 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 · 4 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 · 6 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 · 6 months ago
    how do i make this work on around 150k rows count. Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 6 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 · 8 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 · 5 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 · 7 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 · 9 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 · 9 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 · 9 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 · 9 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 · 11 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 · 11 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 · 11 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 · 11 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 · 11 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 · 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 · 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.
    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 · 10 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 · 10 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