Разделить excel на несколько файлов
Обновлено: 05.07.2024
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?
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!
Does this VBA code not work if there are other characters like -, () are in the column that we want to split? If not, how can I update the code to control for that? hi,I 've to do un upgrade in this code. More specific, i have a Dataset("Data") to split in multiple sheet and this code it's good to me. But in my new sheet i don't want all column from the orignal data but only specific column. How can i do?Hi there, is there a limit on the number of rows in the worksheet you need to split? I have 150,000 rows, that need to be split into diff worksheets based on name of company - this VBA is not working If you have tens of thousands rows data need to be split, I will recommend you to use the Kutools for Excel's Split Data feature, with this feature, you can achieve the job quickly and easily. You can download and free trail 30 days. Please try, thank you! Is there anyway to do this in a way that will maintain the page layout/print set up? header, footer, gridlines, repeat rows, lanscape, narrow margins, fit columns on page, etc? Just used this today and the VB Script was exactly what I needed. Thank you!
But is there any option to keep transferred datas to each excel sheet, if the master sheet is updating daily? Hi, question, is there a good way to ONLY bring in certain columns to the split sheets, instead of all? Dude this is brilliant. saved me many hours of work. Thank you.
Nothing happens. I run the code, it prompts me for the header & column info, I input it, and then nothing. Not even an error to point me in the right direction.
Thank you so much for this code it has helped me over the last few months amazingly.
However, since last month this code has not working for me. It only creates sheets with the name but data, format as well as headers are not carrying over.
I have changed my computer though not sure if this has to do something with it. I have been using the same code and steps as specified above.
I Have problem with borders. In each new table the bottom border is missing. How can I change to have it paste starting at cell a10 instead of a1 on the new sheets?Hi there,
Thank you so much for this post. It is really very helpful.
I have another situation that requires me to split salary data per department so that each manager will receive a workbook for their team only. I do not mail these out to managers, I simply safe it in their respective folder on the server.
I use this code, but this code does not split the worksheet into separate files. Can you please help me to modify this code so that it will create separate workbooks for me in the same directory as the master sheet?
Извлекайте нужные страницы из документов XLSX легко, быстро и точно. Гибкая утилита для бесплатного разделения XLSX на независимые страницы онлайн. Для печати или отправки вашего XLSX по сети вам может потребоваться разделить Excel на части разными способами. Разделитель Excel поддерживает несколько режимов извлечения страниц: разделить XLSX по каждой странице, по нечетным и четным страницам, по заданному номеру страницы или по диапазону страниц.
Не стоит печатать или отправлять по почте Excel лишние страницы. Экономьте время и деньги с помощью Бесплатного Разделителя XLSX для немедленного разделения страниц XLSX. Чтобы разделить Excel на части, просто задайте требуемый режим разделения и нажмите кнопку SPLIT. Наше онлайн-приложение совершенно бесплатно и не требует регистрации на сайте.
Простой сервис для бесплатного извлечения страниц из Excel Онлайн
Вы можете делить документы на части в операционных системах Windows, Linux, Mac и iOS. Наше онлайн-приложение работает во всех современных веб-браузерах и не требует установки какого-либо плагина или дополнительного ПО. Онлайн Разделитель XLSX реализован на базе Aspose API, который используется ведущими компаниями из списка Fortune 100 в 114 странах.
Программная платформа Aspose Words
Онлайн-приложение Excel Splitter разработано на базе программной платформы Aspose Words. Наша компания разрабатывает современные высокопроизводительные решения для обработки документов для различных ОС и языков программирования:
- Загрузите Excel файлы, чтобы бесплатно разделить их онлайн.
- Укажите параметры операции и нажмите кнопку, чтобы разделить страницы в Excel.
- Загрузите разделенный Excel для просмотра или отправьте ссылку для скачивания на email.
- Выходные файлы будут удалены с наших серверов через 24 часа, а ссылки для скачивания перестанут работать.
Попробуйте наш бесплатный аддон Aspose Mail Merge для отправки настраиваемых массовых писем с помощью Gmail.
Если файл сохранён на диске, можно так:
1. Открываете книгу с данными на нужном листе
2. Заходите в VBA (Alt+F11)
3. Выбираете в меню Insert -> Module
4. Вставляете нижеприведённый код
5. Нажимаете F5 (не сохраняете исходный файл)
Никакие C++ запускать не надо.
Заранее спасибо
kolyayolo, благодарю за замечание (обновил код), и хороший вопрос.
Для переноса ширины колонок нужно после объявления переменных сохранить значения ширины колонок в массив:
Затем, после вставки данных перенести значения ширины колонок из массива:
Спасибо за решение, искал везде, ваш подошел идеально! Единственный вопрос - а как сделать, чтобы полученные таблицы-файлы были "упакованы" в умные таблицы на выходе? Я не силен в VBA, подскажете какой код и куда его вписать?
Добавил код, который добавляет умную таблицу к диапазону, но есть проблема. У меня файлы формируются из заранее подготовленной умной таблицы, т.е. она разбивается на части. И этот (ваш) код получается формирует файлы с не отформатированными диапазонами, а последний файл именно форматируется в умную таблицу (как бы унаследует формат из исходного файла). Т.е. не все сформированные файлы с умными таблицами получаются, а только последний. А мне нужно, чтобы все были оформлены в умные таблицы. Я добавил код, который добавляет формат в полученные файлы, вот такое у меня получилось:
kolyayolo, alcompstudio, на технических ресурсах принято выражать свою положительную оценку кнопкой "Нравится", тем самым указывая на полезность материала.
Для создания умной таблицы для всего активного листа с именем "Table_1" используется следующий метод (четвёртый параметр указывает на наличие заголовков):
Если у вас есть книга Excel с большим количеством листов, нет простого способа разделить каждый из этих листов на отдельные файлы Excel и сохранить отдельно. Это может потребоваться, когда вы составляете листы за разные месяцы или регионы, продукты / клиенты и хотите быстро получить отдельную книгу для каждого листа (в виде файла Excel или PDF).
Хотя есть способ вручную разделить листы на отдельные книги, а затем сохранить их, он неэффективен и подвержен ошибкам.
В этом руководстве я дам вам простой код VBA, который вы можете использовать, чтобы быстро (за несколько секунд) разделить все рабочие листы на отдельные файлы, а затем сохранить их в любой указанной папке.
Разделить каждый лист на отдельный файл Excel
Предположим, у вас есть рабочая тетрадь, как показано ниже, где у вас есть лист на каждый месяц.
Чтобы разделить эти листы в отдельный файл Excel, вы можете использовать приведенный ниже код VBA:
Перед использованием приведенного выше кода VBA необходимо убедиться в нескольких вещах:
- Создайте папку, в которой вы хотите получить все полученные файлы.
- Сохраните основной файл Excel (в котором есть все нужные вам рабочие листы в виде отдельных файлов) в этой папке.
Как только вы это сделаете, вы можете поместить указанный выше код VBA в файл и запустить его.
Приведенный выше код написан таким образом, что он выбирает местоположение папки, используя путь к файлу (в котором выполняется код). Вот почему важно сначала сохранить файл в папке, а затем использовать этот код.
Как работает код VBA. В приведенном выше коде используется простой цикл For Next, который проходит через каждый рабочий лист, создает копию рабочего листа в книге Excel, а затем сохраняет эту книгу Excel в указанной папке (которая совпадает с основной файл со всеми листами).
Ниже приведены шаги по размещению этого кода VBA в книге Excel (они будут такими же для всех других методов, показанных в этом руководстве):
Куда поставить этот код?
Ниже приведены шаги по размещению кода в редакторе Visual Basic, где он может быть выполнен:
Вышеупомянутые шаги мгновенно разделят рабочие листы на отдельные файлы Excel и сохранят их. Это займет всего секунду, если у вас меньше листов. Если у вас много, это может занять некоторое время.
Имя каждого сохраненного файла совпадает с именем листа в основном файле.
Поскольку вы поместили код VBA в книгу Excel, вам необходимо сохранить его в формате .XLSM (который является форматом с поддержкой макросов). Это гарантирует, что макрос будет сохранен и будет работать, когда вы откроете этот файл в следующий раз.
Обратите внимание, что я использовал в коде строки Application.ScreenUpdating = False и Application.DisplayAlerts = False, чтобы все происходило в серверной части и не видеть, что происходит на вашем экране. Как только код запускается, разделяет листы и сохраняет их, мы возвращаем им значение ИСТИНА.
Рекомендуется создать резервную копию основного файла (в котором есть листы, которые вы хотите разделить). Это гарантирует, что вы не потеряете свои данные в случае, если что-то пойдет не так, или если Excel решит замедлить работу или дать сбой.
Разделить каждый рабочий лист и сохранить как отдельные PDF-файлы
Если вы хотите разделить листы и сохранить их как файлы PDF вместо файлов Excel, вы можете использовать следующий код:
Перед использованием этого кода необходимо убедиться в следующем:
- Создайте папку, в которой вы хотите получить все полученные файлы.
- Сохраните основной файл Excel (в котором есть все нужные вам рабочие листы в виде отдельных файлов) в этой папке.
Приведенный выше код разделяет каждый лист в файле Excel и сохраняет его в формате PDF в той же папке, где вы сохранили основной файл Excel.
Разделите только те рабочие листы, которые содержат слово / фразу, в отдельные файлы Excel
Если у вас много листов в книге, и вы хотите разделить только те листы, в которых есть определенный текст, вы также можете это сделать.
Например, предположим, что у вас есть файл Excel, содержащий данные за несколько лет, и каждый лист в файле имеет номер года в качестве префикса. Что-то вроде показанного ниже:
Теперь предположим, что вы хотите разделить все листы на 2020 год и сохранить их как отдельные файлы Excel. Для этого вам нужно как-то проверить имя каждого рабочего листа, и только те листы, которые имеют номер 2020, должны быть разделены и сохранены, а остальные должны быть оставлены нетронутыми.
Это можно сделать с помощью следующего макроса VBA:
В приведенном выше коде я использовал переменную TexttoFind, которой изначально было присвоено значение «2020».
Затем код VBA использует цикл For Next в VBA для просмотра каждого рабочего листа, а затем проверяет имя каждой функции INSTR рабочего листа. Эта функция проверяет, есть ли в имени листа слово 2020 или нет. Если это так, он вернет номер позиции, в которой он нашел этот текст (в данном случае это 2020).
И если он не находит искомый текст, он возвращает 0.
Это используется с условием IF Then. Поэтому, если в имени листа есть текстовая строка 2020, оно будет разделено и сохранено как отдельный файл. И если у него нет этой текстовой строки, условие IF не будет выполнено, и ничего не произойдет.
Читайте также: