Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

Как разбить данные на несколько листов на основе столбца в Excel?

Предположим, у вас есть рабочий лист с огромными строками данных, и теперь вам нужно разбить данные на несколько рабочих листов на основе Имя столбец (см. следующий снимок экрана), а имена вводятся случайным образом. Может быть, вы можете сначала отсортировать их, а затем скопировать и вставить их один за другим в другие новые рабочие листы. Но для повторного копирования и вставки потребуется ваше терпение. Сегодня я расскажу о некоторых быстрых приемах решения этой задачи.

документ разбивает данные по столбцам 1

Разделить данные на несколько листов на основе столбца с кодом VBA

Разделите данные на несколько листов на основе столбца с помощью Kutools for Excel


Разделить данные на несколько листов на основе столбца с кодом VBA

Если вы хотите быстро и автоматически разделить данные на основе значения столбца, следующий код VBA - хороший выбор. Пожалуйста, сделайте так:

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модульи вставьте следующий код в окно модуля.

Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub

3, Затем нажмите F5 нажмите клавишу для запуска кода, и появится окно подсказки, напоминающее вам о выборе строки заголовка, см. снимок экрана:

документ разбивает данные по столбцам 7

4. А затем нажмите OK и во втором поле запроса выберите данные столбца, которые вы хотите разделить на основе, см. снимок экрана:

документ разбивает данные по столбцам 8

5, Затем нажмите OK, и все данные на активном листе разбиваются на несколько листов по значению столбца. И разделенные листы названы с именами разделенных ячеек. Смотрите скриншот:

документ разбивает данные по столбцам 2

Внимание: Разделенные листы помещаются в конец книги, где находится главный рабочий лист.


Разделите данные на несколько листов на основе столбца с помощью Kutools for Excel

Как новичку в Excel, этот длинный код VBA несколько сложен для нас, и большинство из нас даже не знает, как изменить код по своему усмотрению. Здесь я представлю вам многофункциональный инструмент -Kutools for Excel, Его Разделить данные Утилита не только может помочь вам разделить данные на несколько листов на основе столбца, но также может разделить данные по количеству строк.

Примечание:Чтобы применить это Разделить данные, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон данных, которые вы хотите разделить.

2. Нажмите Kutools Plus > Рабочий лист > Разделить данные, см. снимок экрана:

документ разбивает данные по столбцам 3

3. В Разделить данные на несколько листов диалоговое окно, вам необходимо:

1). Выбрать Конкретный столбец вариант в Сплит на основе раздел и выберите значение столбца, по которому вы хотите разделить данные, в раскрывающемся списке. (Если у ваших данных есть заголовки, и вы хотите вставить их в каждый новый разделенный лист, проверьте У моих данных есть заголовки опция).

2). Затем вы можете указать имена разделенных листов под Имя нового листа раздел, укажите правила имен рабочих листов из Правила раскрывающийся список, вы можете добавить Префикс or Суффикс также для имен листов.

3). Щелкните значок OK кнопка. Смотрите скриншот:

документ разбивает данные по столбцам 4

4. Теперь данные разделены на несколько листов в новой книге.

документ разбивает данные по столбцам 5

Нажмите, чтобы загрузить Kutools for Excel и бесплатную пробную версию сейчас!


Разделите данные на несколько листов на основе столбца с помощью Kutools for Excel

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Загрузите бесплатную пробную версию прямо сейчас!


Связанная статья:

Как разбить данные на несколько листов по количеству строк?


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Say something here...
symbols left.
You are guest
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.
    Dean · 5 years ago
    works brilliantly but I want to use a button with it as I have created a template that I reuse and just copy the data over.

    Needed urgently
    • To post as a guest, your comment is unpublished.
      Ben · 5 years ago
      Hi Dean,

      This is Easy enough!

      You will need to enable to Developer Tab, to do this, go to:
      File - Options - Customise Ribbon - Then Tick the "Developer" box on the right hand list.
      After this you can Click the tab along the top and hit "insert", choose your button and where to put it. A wizard will appear and ask for a Macro - hit "New" - this then opens up the VBA page and you can add it in :)

      Now you can do this at the click of a button!
  • To post as a guest, your comment is unpublished.
    Natalie · 5 years ago
    When I used the VBA code for some reason it took some of the data and not all. It created blank tabs for the data it did not take. It would name the the tab sheet2 and no data would be present in those tabs. Any ideas on how to fix this?
    • To post as a guest, your comment is unpublished.
      Ben · 5 years ago
      Hi Natalie,

      This is because the data in the column you are sorting on is too long - so it doesnt fit in the name of the sheet.
      In the VBA if an error occurs like this it just skips it!

      Hope that helps?
  • To post as a guest, your comment is unpublished.
    tickelmepinc · 5 years ago
    Hi All,

    The above code works for me, but the column i need to split is a date column. My date format is (080516) and it must stay this way. Currently the code is not picking up this column becauase these values formatted as "custom" and the code line Dim vcol As Integer is not picking it up.

    How do i change it so i can get it to filter on my column of custom formatting. Right now it splits the data into multiple worksheets, but not data is pulled. only the header information.

    Thank you
  • To post as a guest, your comment is unpublished.
    Rohit · 5 years ago
    Hi,

    It works fine with a little tweaks to the code. But the data is getting populated without the headers. The code that i am using is:

    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 = 5
    Set ws = Sheets("Readiness Data")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1:AC1"
    titlerow = ws.Range(title).Cells(44).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.
    Rohit · 5 years ago
    Hi,

    Though the sheets are getting split, the data is reflecting only in the first tab(for the first row). Rest all tabs are blank.
  • To post as a guest, your comment is unpublished.
    Vy nguyen · 5 years ago
    I found the VBA super helpful for my problem.

    However, when I need to modify it, I am not sure how.

    The column I need to have the split is the 11th column, which I was able to modify.

    However, it is not splitting correctly because of the format of this column. The formatting needs to stay this way – customized. I was able to get the tabs to split based on the information in the column, but no data shows.

    currently the column format is 082816 (custom in excel), which means 08/28/16). I believe this is due to the code: Dim vcol As Integer. These are not integers so it is not finding anything hence no data is copied?

    How do i modify the 'dim vcol as integer' part so that it works with my formatting?
    Thank you
  • To post as a guest, your comment is unpublished.
    Sneha · 5 years ago
    Hey,
    thanks it worked for me.. It was a magic...

    My only requirement is on the other sheets, I want the Data to be displayed from A100 and not from first first cell i.e. A1.. Please can you tell the feild to be changed for it...

    Thank you
  • To post as a guest, your comment is unpublished.
    julius D · 5 years ago
    thanks you very much for your support
    the [quote][u]code work well [/u][/quote]
  • To post as a guest, your comment is unpublished.
    Swapnil · 5 years ago
    Hi Guys please help i have use below VBA code for Automatically split rows into different workbook but i have get error of Run -Time error 1004 (ActiveSheet.Columns.AutoFilter Field:=Columns(vColumn).Column, Criterial:=vfilter) .

    Sub split()
    Dim wswb As String
    Dim wssh As String

    wswb = ActiveWorkbook.Name
    wssh = ActiveSheet.Name

    vColumn = InputBox("please indicate which column (i.e. A,B,C,...), you would like to split by", "column selection")

    Columns(vColumn).Copy
    Sheets.Add
    ActiveSheet.Name = "_Summary"
    Range("A1").PasteSpecial
    Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes

    vCounter = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To vCounter
    vfilter = Sheets("_Summary").Cells(i, 1)
    Sheets(wssh).Activate
    ActiveSheet.Columns.AutoFilter Field:=Columns(vColumn).Column, Criterial:=vfilter
    Cells.Copy
    Workbooks.Add
    Range("A1").PasteSpecial
    If vfilter "" Then
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split Results\" & vfilter
    Else
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Split Results\_empty"
    End If

    ActiveWorkbook.Close
    Workbooks(wswb).Activate
    Next i

    Sheets("_Summary").Delete


    End Sub
  • To post as a guest, your comment is unpublished.
    Rahul · 5 years ago
    Above code work fine.

    I want specify which column should pasted to now sheet like A-I columns only.

    I cant use cell function in above code.

    Can some guide me to solve above error.

    where i need to make change in code.
  • To post as a guest, your comment is unpublished.
    Loren Kumar · 5 years ago
    this has been great help for me.
    is there a way to do the opposite?
    I have multiple worksheet with different names, I want to create a master sheet with data from each of the worksheet
    thanks
  • To post as a guest, your comment is unpublished.
    Steven · 5 years ago
    This code appears to be matching what I need, however, there are two issues that I am running into. First, my "Sheet1" includes the names of each respective worksheets in column A and is adding that additional column to each of the sheets. Which is not how they are currently formatted. Is there a way to change what is being transferred? In my case the data I need from "Sheet1" is in columns B-H and then need to be in columns A-G for each of the respective sheets. Second issue, how can I insert this data rather than writing over existing data? If these two items can be answered, this would be a game changer for me. Thanks!
  • To post as a guest, your comment is unpublished.
    Liam · 5 years ago
    Hi all,

    I have a header from rows 1 to 14 that I want to carry over to each separated sheet (my title row is row 14). How do I alter the code above to achieve this?

    Thanks in advance!
  • To post as a guest, your comment is unpublished.
    Stratos · 5 years ago
    Hello everybody,

    I have a question. Everything runs perfectly (but slowly), but there is a problem.
    I want to make the first 3 rows as headers in each tab. Just like the first one.

    The data begin from row 4 till the end. So, how can I edit the code to get the first 3 rows not only the first one?

    Thank you
  • To post as a guest, your comment is unpublished.
    RUDY · 5 years ago
    Is there a way to get this code to transfer formulas that may be in the sheet to the tabs?
  • To post as a guest, your comment is unpublished.
    Aaron · 5 years ago
    Thanks for the code, but is only seems to work for some of the data. There are big gaps where Sheet1-Sheet5 are blank, but Sheet6 pulled the information as it was supposed to. Any guidance on how to correct this would be greatly appreciated. Thanks!
  • To post as a guest, your comment is unpublished.
    step · 5 years ago
    How to do if the number of column that I want to split the data based on is more than 1?
  • To post as a guest, your comment is unpublished.
    Jeremiah · 5 years ago
    Is there a way to split the data but keep the formulas that are attached to the cells? is there a pasteformula that can be inserted in there somewhere?
  • To post as a guest, your comment is unpublished.
    Nadeem Iqbal · 5 years ago
    Thanks a lot for sharing a much needed programming code.

    Can you please tell me how to change the code so that only the last filled/data row be processed in this same way?
  • To post as a guest, your comment is unpublished.
    Nadeem Iqbal · 5 years ago
    Thanks a lot for sharing a much needed programming code.

    Can you please tell me how to process the entire code for just carrying out the same process on just the last filled/data row?
  • To post as a guest, your comment is unpublished.
    Tom · 5 years ago
    The code works great for names and titles, but does anyone know how to separate the column into new tabs based on date? What do I have to change in the macro?
  • To post as a guest, your comment is unpublished.
    July · 5 years ago
    Hi,
    Thank you for the code, it was very useful and minor chances have been made to match my situation. Can you please give me a suggestion regarding the cell dimension shown in master sheet? Multiple sheets were created, master sheet was hidden, but the cell dimension wasn’t copied to the other sheets.
    Thank You,

    Julia
    Thanks for the code it works for me!

    I only have two questions/remarks.

    1 th[quote name="Pieter"]Hi,
    Thanks for the code it works for me!

    I only have two questions/remarks.

    1 the copied data does not inlclude the layout of the original file. Would it be possible to copy the data as table with autofilter?

    2 the copied data does not seem to be bounded to the title range.
    Is it possbible to adjust the code for a specific range or tablename?

    These adjustment would be very helpfull.

    Regards,

    Pieter[/quote]

    Is it possbible to adjust the code for a specific range or tablename?

    These adjustment would be very helpfull.

    Regards,

    Pieter[/quote]
  • To post as a guest, your comment is unpublished.
    Jelena · 5 years ago
    Thank you!!!!! Thank you!!!! You are my heroe :)
  • To post as a guest, your comment is unpublished.
    John · 5 years ago
    Anybody know how to modify this code so that the formulas are carried over to the new sheets rather than just values?
    • To post as a guest, your comment is unpublished.
      Jelena · 5 years ago
      [quote name="John"]Anybody know how to modify this code so that the formulas are carried over to the new sheets rather than just values?[/quote]
      I need to preserve formulas, too. Thank you if any body could help.
  • To post as a guest, your comment is unpublished.
    Judalyn · 5 years ago
    The code seems o be working fine, however I am getting blank sheets for others, please help
  • To post as a guest, your comment is unpublished.
    NK · 5 years ago
    HI!

    Your codes is running perfectly, Really [i]thanks[/i] for the code,
    Its splitting the data but I've a total colum there in each sheets,

    Can you tell me how to handle that? while inserting data, a new data overrides the existing Total values so I'll need to insert a new before splitting the data.

    Can you help me in that?

    Thanks.
  • To post as a guest, your comment is unpublished.
    satya sai · 6 years ago
    can some one help how to delete heading from this code
  • To post as a guest, your comment is unpublished.
    sai · 6 years ago
    how to remove the header ? from the code ? which one i have to delete please help
  • To post as a guest, your comment is unpublished.
    Beth · 6 years ago
    thanks, works great!
  • To post as a guest, your comment is unpublished.
    Henrik · 6 years ago
    Hi,
    the code works fine for most of the data. However, some sheets are created without the title and without data. This happens without a (for me) visible pattern. The sheet is created but the entire range of data is simply not copied into the sheet and the sheet name is e.g. Sheet4 if is it the 4th sheet. When it happens, it happens for the entire range of that specific title area.
    It doesn't stop the process, as some sheets are correctly created later during the macro...

    Screendump: [url]https://www.dropbox.com/s/xnjcurt4ub01d1j/Screen%20Shot%202015-10-02%20at%2017.27.28.png?dl=0[/url]
    • To post as a guest, your comment is unpublished.
      John · 6 years ago
      I had the same issue and it looks to me like the number of characters in the cells that were skipped might be too long? Anyone think that might be the problem??

      [quote name="Henrik"]Hi,
      the code works fine for most of the data. However, some sheets are created without the title and without data. This happens without a (for me) visible pattern. The sheet is created but the entire range of data is simply not copied into the sheet and the sheet name is e.g. Sheet4 if is it the 4th sheet. When it happens, it happens for the entire range of that specific title area.
      It doesn't stop the process, as some sheets are correctly created later during the macro...

      Screendump: [url]https://www.dropbox.com/s/xnjcurt4ub01d1j/Screen%20Shot%202015-10-02%20at%2017.27.28.png?dl=0[/url][/quote]
      • To post as a guest, your comment is unpublished.
        Henrik · 6 years ago
        Hi John - You are right!
        It was the length/number of characters in the cells that are used as sheet-names... i shortened them a bit (to 30 I think) and then the problem disappeared.
        :)
  • To post as a guest, your comment is unpublished.
    Rhys · 6 years ago
    Hi

    This code works perfectly for seven tabs, however after that I have the following error "excel cannot complete this task with available resources choose less data or close other application"

    my data on sheet1 is A-E with 16133 rows.

    Is this due to my pc only running 4gb ram?
  • To post as a guest, your comment is unpublished.
    MF · 6 years ago
    Hello

    When I try to use this it only copies the title into each new workbook. What do I need to change in order to have all rows associated with a given column value into the new worksheets. I have Data in Columns A2 through N3200
  • To post as a guest, your comment is unpublished.
    McMoeLee · 6 years ago
    Really great Work!! Helps me a lot!
    Copy, Little adapt for my needs, works like a charm!
  • To post as a guest, your comment is unpublished.
    mona · 6 years ago
    IF I am trying the code for huge data, it is showing runtime error in "For i = 2 To lr" line.
    Please help me with this
    • To post as a guest, your comment is unpublished.
      Amanda · 6 years ago
      I ran into the same issue. I found that if I cut down the size of my table (It went from around 40,000 to about 20,000 rows) I no longer got the error.

      What I'm not sure is: What's the max number of rows the macro can support? I could play around with it... It's somewhere between 20k and 40k!
  • To post as a guest, your comment is unpublished.
    Ellen · 6 years ago
    This is great!

    Can the same code be used to pull data from two worksheets? For example, I have a workbook with work completed and another with amount to bill. I'd like to combine the two together on a worksheet for each company with work completed on top and amount to bill below.

    Any help is greatly appreciated, thanks!
  • To post as a guest, your comment is unpublished.
    Marissa · 6 years ago
    Code works great on a small sample set of data but when I run it on my large data set (7000 rows and 53 columns) it creates the correct worksheets with no data on them. Any ideas?
  • To post as a guest, your comment is unpublished.
    Yzel · 6 years ago
    [quote name="MieMie"]The code worked like a charm for a smaller data (less than 1200 rows). I tried to use on the bigger worksheet with (17000 rows) and it just crashed after splitting into 10-12 sheets. So we tried to split the original data into 3 different workbooks and still shut us down. We have Windows 7 and our computers are not that slow either. Do you suggest any limited rows on data to use this code safely? Any suggestion would be appreciated.[/quote]



    I have same issue as above.appreciate all your help please
  • To post as a guest, your comment is unpublished.
    Prithwiraj Kundu · 6 years ago
    it s of a great help. thanks a lot for the code and example

    However I need the data to split based on other than column A( as shown in the example)it may be based on column B,C,D,... what would be the difference in coding in that case ?
    • To post as a guest, your comment is unpublished.
      Deanna · 6 years ago
      change vcol to be the number corresponding to the column, example, column B would be 2
  • To post as a guest, your comment is unpublished.
    rajesh t · 6 years ago
    Working Perfect, Need know how copy only the few columns?
  • To post as a guest, your comment is unpublished.
    cheryle · 6 years ago
    Do you have a code for splitting by row header instead of column?
  • To post as a guest, your comment is unpublished.
    Andrew Hope · 6 years ago
    Works perfect! But what if I want to add totals to each of the newly created sheets, given that each sheet has a different amount of rows.
  • To post as a guest, your comment is unpublished.
    dnyaga · 6 years ago
    Great stuff,Have been doing this manually.Will save alot of time
  • To post as a guest, your comment is unpublished.
    Patrick S · 6 years ago
    I cannot get this to transfer more that one row for the header.
    I need the first 10 rows transferred to each sheet. I have tried changing title = "A1:C1" to: title = "A1:L10" and title = "1:10" but it still just transfers the first row.
    Please help. Thank you
  • To post as a guest, your comment is unpublished.
    Patrick · 6 years ago
    Hi,
    This works great with a one line header but my sheet has a 10 line header. I tried changing the title definition to
    title = "A1:L10"
    and
    title = "1:10"
    neither worked.

    Thanks
    • To post as a guest, your comment is unpublished.
      Eric · 5 years ago
      Hi
      You probably got it cracked
      You need to change the value down the bottom of the code where it says . range ("A3") to in your case ("A10") this is the location of the copy command.
      Hope that helped
      • To post as a guest, your comment is unpublished.
        Allan · 4 years ago
        change the following row:

        ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")

        you will want:

        ws.Range("A" & "A1:L9" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
    • To post as a guest, your comment is unpublished.
      Sarah · 6 years ago
      try title = "A1:A10"
  • To post as a guest, your comment is unpublished.
    Lisachea · 7 years ago
    Thank you so much for this feature. I've wasted a large amount of my time cutting and pasting.
  • To post as a guest, your comment is unpublished.
    Lord Ben · 7 years ago
    Work nicely, for the first 10,712 lines... Then it broke XD
    Apparently my computer isn't powerful enough.
  • To post as a guest, your comment is unpublished.
    Chloe · 7 years ago
    Hi, when i tried to run the macro after pasting the script and amending the necessary, an error was prompted "type mismatch" and when I click in to debug, below line was highlighted..

    For i = 2 To UBound(myarr)

    How should I recitify this error please?

    Appreciate your advice.
  • To post as a guest, your comment is unpublished.
    Umesh · 7 years ago
    thank you. It worked after I did minor changes to the code.
  • To post as a guest, your comment is unpublished.
    kana · 7 years ago
    hi guys

    I am seem to get an error onth eline " for I = 2 to Ir" what is this referring to .
  • To post as a guest, your comment is unpublished.
    Katie · 7 years ago
    Thank you so much for this. The way the macro splits now, it's making sheets for rows that are still considered header rows in my worksheet.Is there a way to change the code so that the header is row 1 through 13?