نصيحة: اللغات الأخرى مترجمة من قبل Google. يمكنك زيارة English نسخة من هذا الرابط.
تسجيل الدخول
x
or
x
x
التسجيل
x

or

كيفية تقسيم البيانات إلى أوراق عمل متعددة استنادا إلى العمود في إكسيل؟

لنفترض أن لديك ورقة عمل مع صفوف ضخمة من البيانات، والآن، تحتاج إلى تقسيم البيانات إلى أوراق عمل متعددة على أساس اسم العمود (انظر الصورة التالية) ، ويتم إدخال الأسماء بشكل عشوائي. ربما يمكنك فرزها أولاً ، ثم نسخها ولصقها واحدة تلو الأخرى في أوراق عمل جديدة أخرى. لكن هذا سيحتاج إلى صبرك للنسخ واللصق مرارًا وتكرارًا. اليوم ، سأتحدث عن بعض الحيل السريعة لحل هذه المهمة.

تقسيم بيانات doc بواسطة الأعمدة 1

تقسيم البيانات إلى أوراق عمل متعددة استنادا إلى عمود مع التعليمات البرمجية فبا

تقسيم البيانات إلى أوراق عمل متعددة استنادا إلى عمود مع كوتولس ل إكسيل

تقسيم البيانات إلى أوراق عمل متعددة استنادًا إلى عدد عمود أو صفوف محددة في ورقة العمل:

إذا كنت تريد تقسيم ورقة عمل كبيرة إلى أوراق متعددة استنادًا إلى بيانات عمود محدد أو عدد الصفوف ، فإن كوتولس ل إكسيل's تقسيم البيانات ميزة يمكن أن تساعدك على حل هذه المهمة بسرعة وسهولة.

تقسيم بيانات doc بواسطة الأعمدة 6

كوتولس ل إكسيل: مع أكثر من شنومكس مفيد اكسل الإضافية، مجانا في محاولة مع عدم وجود قيود في أيام شنومكس. تحميل وتجريبية مجانية الآن!

تقسيم البيانات إلى أوراق عمل متعددة استنادا إلى عمود مع التعليمات البرمجية فبا

إذا كنت ترغب في تقسيم البيانات على أساس قيمة العمود بسرعة وتلقائيا، رمز فبا التالي هو خيار جيد. يرجى القيام بذلك على النحو التالي:

1. اضغط باستمرار ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

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 مفتاح لتشغيل التعليمات البرمجية ، وظهر مربع مطالبة لتذكيرك بتحديد صف الرأس ، انظر لقطة الشاشة:

تقسيم بيانات doc بواسطة الأعمدة 7

4. ثم انقر فوق OK زر ، وفي مربع المطالبة الثاني ، يرجى تحديد بيانات العمود التي تريد تقسيمها استنادًا إلى ، راجع لقطة الشاشة:

تقسيم بيانات doc بواسطة الأعمدة 8

5. ثم اضغط OK، ويتم تقسيم جميع البيانات في ورقة العمل النشطة إلى أوراق عمل متعددة حسب قيمة العمود. ويتم تسمية أوراق العمل المقسمة بأسماء الخلايا المنقسمة. انظر لقطة للشاشة:

تقسيم بيانات doc بواسطة الأعمدة 2

ملاحظة:: يتم وضع أوراق العمل المقسمة في نهاية المصنف حيث توجد ورقة العمل الرئيسية.

تقسيم البيانات إلى أوراق عمل متعددة استنادا إلى عمود مع كوتولس ل إكسيل

كمبتدئ إكسيل، وهذا رمز فبا طويلة من الصعب إلى حد ما بالنسبة لنا، ومعظمنا حتى لا يعرفون كيفية تعديل التعليمات البرمجية كما حاجتنا.

هنا، وسوف أعرض لكم أداة متعددة الوظائف -كوتولس ل إكسيل، في تقسيم البيانات فائدة ليس فقط يمكن أن تساعدك على تقسيم البيانات إلى أوراق عمل متعددة على أساس العمود، ولكن أيضا يمكن تقسيم البيانات حسب عدد الصفوف.

كوتولس ل إكسيل : مع أكثر من شنومكس مفيد اكسل الإضافية، مجانا في محاولة مع عدم وجود قيود في أيام شنومك.
انتقل إلى تنزيل
تجريبية مجانية أيام شنومكشراء
باي بال / ميكومرس

إذا كنت قد قمت بتثبيت كوتولس ل إكسيل، يرجى القيام بما يلي:

1. حدد نطاق البيانات التي تريد تقسيمها.

2. انقر كوتولس بلاس > ورقة العمل > تقسيم البيانات، انظر الصورة:

تقسيم بيانات doc بواسطة الأعمدة 3

3. في تقسيم البيانات إلى أوراق عمل متعددة مربع الحوار، تحتاج إلى:

1). تحديد عمود محدد الخيار في تقسيم على أساس ، واختيار قيمة العمود الذي تريد تقسيم البيانات استنادا إليه في القائمة المنسدلة. (إذا كانت بياناتك تحتوي على رؤوس وتريد إدراجها في كل ورقة عمل تقسيم جديدة، يرجى التحقق من ذلك تحتوي بياناتي على رؤوس اختيار.)

2). ثم يمكنك تحديد أسماء ورقة العمل تقسيم، تحت اسم أوراق العمل الجديدة ، حدد قواعد أسماء أوراق العمل من قواعد القائمة المنسدلة، يمكنك إضافة بادئة or لاحقة لأسماء الورقة كذلك.

3). انقر على OK زر. انظر لقطة الشاشة:

تقسيم بيانات doc بواسطة الأعمدة 4

4. الآن يتم تقسيم البيانات إلى أوراق عمل متعددة في مصنف جديد.

تقسيم بيانات doc بواسطة الأعمدة 5

انقر لتحميل كوتولس ل إكسيل ونسخة تجريبية مجانية الآن!

تقسيم البيانات إلى أوراق عمل متعددة استنادا إلى عمود مع كوتولس ل إكسيل

كوتولس ل إكسيل يتضمن أكثر من أدوات إكسوم إكسيل مفيد. مجانا في محاولة مع عدم وجود قيود في أيام شنومكس. تنزيل النسخة التجريبية المجانية الآن!

مقالة ذات صلة:

كيفية تقسيم البيانات إلى أوراق عمل متعددة حسب عدد الصفوف؟

Kutools for Excel يحل معظم مشاكلك ، ويزيد من إنتاجيتك بنسبة 80٪

  • إعادة استخدام: إدراج بسرعة الصيغ المعقدة ، الرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة المرور إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • سوبر الفورمولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (بسهولة قراءة وتحرير أعداد كبيرة من الخلايا) ؛ لصق على المدى المصفى...
  • دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ انقسام خلايا المحتوى ؛ الجمع بين تكرار الصفوف / الأعمدة... منع الخلايا المكررة. مقارنة النطاقات...
  • حدد تكرار أو فريد الصفوف. حدد صفوف فارغة (جميع الخلايا فارغة) ؛ سوبر البحث والعثور غامض في العديد من المصنفات ؛ اختيار عشوائي ...
  • نسخة طبق الأصل خلايا متعددة دون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة. إدراج الرصاصات، مربعات الاختيار والمزيد ...
  • استخراج النص، إضافة نص ، حذف حسب الموضع ، إزالة الفضاء. إنشاء وطباعة مجاميع ترحيل الصفحات ؛ تحويل بين محتوى الخلايا والتعليقات...
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ تصنيف متقدم حسب الشهر / الأسبوع / اليوم ، التردد وأكثر ؛ فلتر خاص بواسطة جريئة ، مائل ...
  • الجمع بين المصنفات وأوراق العمل. دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; دفعة تحويل XLS ، XLSX وقوات الدفاع الشعبي...
  • أكثر من ميزات 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.
    Matt Carrington · 1 years ago
    Hi, great time saver, thanks. I know it is an old post but hopefully someone can help.

    My table needs splitting based on column 1, but column 1 contains dates and the result is that each new sheet doesnt contain any data. It creates the right amount of sheets but doesnt move any data. Any Ideas?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Carrington,
      To split the data based on the date column, you should use the following vba code, but, this code is only applied to date column correctly, if your key column is normal data, please use the code in this article.
      Sub parseData_Date()
      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 xName As String
      Dim xValue As String
      Dim xArrFind As Variant
      Dim xStrReplace As String
      Dim xFNum As Integer
      Dim xRg As Range
      Set ws = Sheets("Master sheet")
      xArrFind = Array(":", "\", "/", "?", "*", "[", "]")
      xStrReplace = "_"
      vcol = 1
      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"
      On Error Resume Next
      For i = 2 To lr
      If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
      Set xRg = ws.Cells(i, vcol)
      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)
      xValue = ""
      xValue = myarr(i)
      xValue = FormatDateTime(xValue)
      xName = xValue
      For xFNum = 0 To UBound(xArrFind)
      xName = Replace(xName, xArrFind(xFNum), xStrReplace)
      Next xFNum
      ws.Range(title).AutoFilter field:=vcol, Criteria1:=xValue & ""
      If Not Evaluate("=ISREF('" & xName & "'!A1)") Then
      Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = xName & ""
      Else
      Sheets(xName & "").Move After:=Worksheets(Worksheets.Count)
      End If
      ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(xName & "").Range("A1")
      Sheets(xName & "").Columns.AutoFit
      Next
      ws.AutoFilterMode = False
      ws.Activate
      End Sub

      Please try it, hope it can help you!
  • 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 · 1 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 · 2 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 · 2 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 · 2 months ago
        Works great!


        Thanks for all your help!
  • To post as a guest, your comment is unpublished.
    Bernhard · 3 months ago
    Thank you, worked excellently!
  • To post as a guest, your comment is unpublished.
    Degardt · 3 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 · 3 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 · 2 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 · 4 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 · 4 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 · 6 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 · 5 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 · 7 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 · 7 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 · 7 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 · 9 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 · 9 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 · 9 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 · 9 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 · 9 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 · 10 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 · 10 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 · 11 months 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 · 11 months 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 · 8 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 · 8 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
  • To post as a guest, your comment is unpublished.
    Mario · 1 years ago
    I use this VBA code to split commission statement. Can a SUM code be added to get a total on column C?
أنجليزي English
en Englishar العربيةزكية-CN 简体中文zh-TW 繁體中文nl Nederlandsfr Françaisde Deutschit Italianoja 日本語ko 한국어pt Portuguêsru Русскийes Español