Разбить листы excel на несколько файлов

Сохранение листов книги как отдельных файлов

Про сборку листов из нескольких книг в одну текущую я уже писал здесь. Теперь разберем решение обратной задачи: есть одна книга Excel, которую нужно «разобрать», т.е. сохранить каждый лист как отдельный файл для дальнейшего использования.

Примеров подобного из реальной жизни можно привести массу. Например, файл-отчет с листами-филиалами нужно разделить на отдельные книги по листам, чтобы передать затем данные в каждый филиал и т.д.

Если делать эту процедуру вручную, то придется для каждого листа выполнить немаленькую цепочку действий (выбрать лист, правой кнопкой по ярлычку листа, выбрать Копировать, указать отдельный предварительно созданный пустой файл и т.д.) Гораздо проще использовать короткий макрос, автоматизирующий эти действия.

Способ 1. Простое разделение

Нажмите сочетание Alt+F11 или выберите в меню Сервис — Макрос — Редактор Visual Basic (Tools — Macro — Visual Basic Editor), вставьте новый модуль через меню Insert — Module и скопируйте туда текст этого макроса:

Sub SplitSheets1()
    Dim s As Worksheet
    For Each s In ActiveWorkbook.Worksheets    'проходим по всем листам в активной книге
        s.Copy       'копируем каждый лист в новый файл
    Next
End Sub

Если теперь выйти из редактора Visual Basic и вернуться в Excel, а затем запустить наш макрос (Alt+F8), то все листы из текущей книги будут разбиты по отдельным новым созданным книгам.

Способ 2. Разделение с сохранением

При необходимости, можно созданные книги сразу же сохранять под именами листов. Для этого макрос придется немного изменить, добавив команду сохранения в цикл:

Sub SplitSheets2()
    Dim s As Worksheet
    Dim wb as Workbook
    Set wb = ActiveWorkbook
    For Each s In wb.Worksheets                                 'проходим во всем листам активной книги
        s.Copy                                                  'сохраняем лист как новый файл
        ActiveWorkbook.SaveAs wb.Path & "" & s.Name & ".xlsx"  'сохраняем файл
    Next
End Sub

Этот макрос сохраняет новые книги-листы в ту же папку, где лежал исходный файл. При необходимости сохранения в другое место, замените wb.Path на свой путь в кавычках, например «D:Отчеты2012» и т.п.

Если нужно сохранять файлы не в стандартном формате книги Excel (xlsx), а в других (xls, xlsm, xlsb, txt и т.д.), то кроме очевидного изменения расширения на нужное, потребуется добавить еще и уточнение формата файла — параметр FileFormat:

ActiveWorkbook.SaveAs wb.Path & "" & s.Name & ".xlsb", FileFormat:=50  

Для основных типов файлов значения параметра FileFormat следующие:

• XLSX = 51
• XLSM = 52
• XLSB = 50
• XLS = 56
• TXT = 42

Полный список всех вариантов можно посмотреть в справке MSDN.

Способ 3. Сохранение в новые книги только выделенных листов

Если вы хотите раскидать по файлам не все листы в вашей книге, а только некоторые, то макрос придется немного изменить. Выделите нужные вам листы в книге, удерживая на клавиатуре клавишу Ctrl или Shift и запустите приведенный ниже макрос:

Sub SplitSheets3()
    Dim AW As Window
    Set AW = ActiveWindow
    For Each s In AW.SelectedSheets
        Set TempWindow = AW.NewWindow    'создаем отдельное временное окно
        s.Copy                           'копируем туда лист из выделенного диапазона
        TempWindow.Close                 'закрываем временное окно
    Next
End Sub

Создавать новое окно и копировать через него, а не напрямую, приходится потому, что Excel не умеет копировать группу листов, если среди них есть листы с умными таблицами. Копирование через новое окно позволяет такую проблему обойти.

Способ 4. Сохранение только выделенных листов в новый файл

Во всех описанных выше способах каждый лист сохранялся в свой отдельный файл. Если же вы хотите сохранить в отдельный новый файл сразу группу выделенных предварительно листов, то нам потребуется слегка видоизменить наш макрос:

Sub SplitSheets4()
    Dim CurW As Window
    Dim TempW As Window
    Set CurW = ActiveWindow
    Set TempW = ActiveWorkbook.NewWindow
    CurW.SelectedSheets.Copy
    TempW.Close
End Sub

Способ 5. Сохранение листов как отдельных PDF-файлов

Этот способ чем-то похож на второй, но листы сохраняются не как отдельные книги Excel, а в формате PDF, что часто требуется, если никто не должен менять документ и увидеть ваши формулы. Обратите внимание, что:

• для этого используется уже другой метод (ExportAsFixedFormat а не Copy)
• листы выводятся в PDF с параметрами печати, настроенными на вкладке Разметка страницы (Page Layout)
• книга должна быть сохранена на момент экспорта

Нужный нам код будет выглядеть следующим образом:

Sub SplitSheets5()
    Dim s As Worksheet

    For Each s In ActiveWorkbook.Worksheets
        s.ExportAsFixedFormat Filename:=ThisWorkbook.Path & "" & s.Name & ".pdf", Type:=xlTypePDF
    Next
End Sub

Способ 6. Готовый макрос из надстройки PLEX

Если лень или нет времени внедрять все вышеописанное, то можно воспользоваться готовым макросом из моей надстройки PLEX:

Ссылки по теме

• Сборка листов из нескольких книг в одну
• Что такое макросы, куда вставлять код на Visual Basic, как их использовать.

Возможно, вам придется разделить большую книгу на отдельные файлы Excel, сохранив каждый рабочий лист книги как отдельный файл Excel. Например, вы можете разделить книгу на несколько отдельных файлов Excel, а затем передать каждый файл другому человеку для обработки. Поступая таким образом, вы можете заставить определенных людей обрабатывать определенные данные и сохранить ваши данные в безопасности. В этой статье будут представлены способы разделения большой книги на отдельные файлы Excel на основе каждого рабочего листа.

1. Разделение книги на отдельные файлы Excel с копированием и вставкой
2. Разделите книгу на отдельные файлы Excel с помощью функции перемещения или копирования
3. Разделить книгу на отдельные файлы Excel с кодом VBA
4. Разделите книгу на отдельные файлы Excel / PDF / CSV / TXT с помощью Kutools for Excel легко

Разделение книги на отдельные файлы Excel с копированием и вставкой

Разделите книгу на отдельные файлы Excel с помощью функции перемещения или копирования

Разделить книгу на отдельные файлы Excel с кодом VBA

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Разделите книгу на отдельные файлы Excel / PDF / CSV / TXT с помощью Kutools for Excel легко

Демонстрация: разделите или сохраните каждый рабочий лист одной книги как отдельные файлы excel / txt / csv / pdf

Статьи по теме:

Введение

Предположим, у нас есть рабочий лист с большим количеством данных, и нам нужно разделить данные на отдельные книги Excel на основе столбца «Категория». Решить данную проблему возможно ручным способом, применяя инструмент фильтрации данных. Однако повторное копирование данных потребует терпения.

Как быстро разделить таблицу Excel на отдельные книги макросом. Пошаговый алгоритм

• Сочетанием клавиш Alt+F11 открываем окно Visual Basic
• Создаем новый модуль Insert -> Module
• Вставляем код

Sub Разделить_столбец_по_книгам()
Const column = 2 'номер столбца, по которому будет происходить разделение.'
Const head = True
Set wbAct = ActiveWorkbook

Set dic = CreateObject("Scripting.Dictionary")

lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).column

arr = Range("A1", Cells(lr, lc)).Value

If head Then fr = 2 Else fr = 1

For i = fr To UBound(arr)
    If Trim(arr(i, column)) <> "" Then dic.Item(arr(i, column)) = dic.Item(arr(i, column)) & "|" & i
Next

iPath = wbAct.Path & Application.PathSeparator & "Result" & Application.PathSeparator
'Result - название папки с результатами'
If Dir(iPath, vbDirectory) = "" Then MkDir iPath

arrDic = dic.keys
Set Rng = Nothing
Application.DisplayAlerts = False
For i = 0 To UBound(arrDic)
rrs = Split(Mid(dic.Item(arrDic(i)), 2), "|")
    If head Then Set Rng = Rows(1)
    For Each rr In rrs
        If Not Rng Is Nothing Then Set Rng = Union(Rows(rr), Rng) Else Set Rng = Rows(rr)
    Next
    Set wb = Workbooks.Add(1)
    Set sh = wb.Sheets(1)
    Rng.Copy
    sh.[A1].PasteSpecial xlPasteColumnWidths
    sh.[A1].PasteSpecial xlPasteAll
    Set Rng = Nothing
    wb.SaveAs iPath & Replace_symbols(arrDic(i)) & ".xlsx", xlOpenXMLWorkbook
    wb.Close False
Next
Application.DisplayAlerts = True
End Sub
'Замена запрещённых символов в имени файла или папки'
Function Replace_symbols(ByVal txt As String) As String
    St$ = "\/~!@#$%^&*=|`'"""
    For i% = 1 To Len(St$)
        txt = Replace(txt, Mid(St$, i, 1), "_")
    Next
    Replace_symbols = txt
End Function

• На второй строке кода, цифру 2 замените на номер столбца, в котором содержится критерий для разбиения
• Сохраните код Ctrl+S
• Сохраните файл Excel, как книгу с поддержкой макросов xlsm
• Выполните макрос Alt+F8

Папка с новыми файлами будет лежать по тому же пути, что и файл, в котором вы запускали макрос

Заключение

С помощью этого руководства и готового кода макроса Вы сможете сэкономить свое время и за несколько минут создать столько книг Excel, сколько уникальных значений в выбранном Вами столбце. Вся остальная информация автоматически перенесется в новые книги.

У нас Вы можете заказать выполнение задач по MS Excel и Google таблицам

А также, пройти бесплатные онлайн курсы по MS Excel с заданиями

Такое бывает довольно часто, делаешь ежегодный отчет, а в нем 12 месяцев, соответственно 12 листов. И нужно разделить этот файл таким образом, чтобы каждый лист стал отдельным файлом.

И конечно же, можно сделать это руками, но это крайне долго и неэффективно.

Я продемонстрирую вам простой код Visual Basic, который выполнит задачу за вас.

Делим файл Excel на несколько файлов по листам

Допустим, у нас есть ежегодный отчет, в котором по листам расписаны показатели компании за каждый месяц. Как на картинке ниже:

Код Visual Basic, который разделит таблицу на несколько файлов по месяцам:

Sub SplitEachWorksheet() 
Dim FPath As String 
FPath = Application.ActiveWorkbook.Path 
Application.ScreenUpdating = False 
Application.DisplayAlerts = False 
For Each ws In ThisWorkbook.Sheets
     ws.Copy
     Application.ActiveWorkbook.SaveAs Filename:=FPath & "" & ws.Name & ".xlsx"
     Application.ActiveWorkbook.Close False 
Next 
Application.DisplayAlerts = True 
Application.ScreenUpdating = True 
End Sub  

Перед тем, как запустите этот код, сделайте следущее:

1. Создайте новую папку, куда поместите результаты работы этого кода;
2. А также, на всякий случай, сделайте копию оригинального файла.

Теперь создайте функцию в Visual Basic и смело запускайте код.

Этот код сам найдет путь до папки с файлом.

Как он работает?

Довольно просто, он открывает каждый лист и сохраняет его как отдельный файл с тем же названием.

Куда поместить этот код?

Пошаговая инструкция:

• Щелкните на «Разработчик»;

• Далее откройте VBA;

• Правой кнопкой мышки на любой лист;.

• Щелкните на «Insert» -> «Module»;

• Поместите наш код в открывшееся окошко;

• Теперь запустите код.

Итак, как только вы запустите код, он сразу же разделит ваш файл на несколько файлов по листам. Это крайне удобно, советую его сохранить. Даже если сейчас он вам не нужен, в будущем обязательно пригодится.

Как я говорил ранее, имя файла такое же, как и имя листа.

Также не забудьте сохранить файл с соответствующим расширением(.XLSM), так как мы используем функции Visual Basic.

В коде я специально сделал так, чтобы вы не видели все что происходит и это вам не мешало. Вы можете исправить это если вам наоборот нужно видеть то, что происходит.

Но также, опять повторюсь, обязательно сделайте копию вашего файла перед использованием функции! Потому что если работа Excel завершится из-за какой-либо ошибки или произойдет еще что-то неожиданное вы можете потерять свои данные!

Делим файл Excel на несколько PDF файлов по листам

Вот код для такого случая:

Sub SplitEachWorksheet() 
Dim FPath As String 
FPath = Application.ActiveWorkbook.Path 
Application.ScreenUpdating = False 
Application.DisplayAlerts = False   

For Each ws In ThisWorkbook.Sheets
     ws.Copy
     Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath & "" & ws.Name & ".xlsx"
     Application.ActiveWorkbook.Close False 
Next   

Application.DisplayAlerts = True 
Application.ScreenUpdating = True 
End Sub  

И снова, не забудьте сначала создать папку и обязательно сделать копию оригинального файла. Код разделит вашу табличку по страницам и создаст для каждой страницы отдельный PDF файл.

Разделите только те рабочие листы, в которых содержится слово/фраза, на отдельные файлы Excel

Бывают и такие ситуации, что отдельный файл нужно создать только для тех страниц, в названии которых есть определенный текст.

Допустим, у вас есть страницы отчета за разные года, в названии каждого листа указан год и месяц. Но вам нужно сохранить только те листы, которые относятся к 2020 году. Как это сделать?

Вот код Visual Basic:

Sub SplitEachWorksheet() 
Dim FPath As String 
Dim TexttoFind As String 
TexttoFind = "2020" 
FPath = Application.ActiveWorkbook.Path 
Application.ScreenUpdating = False 
Application.DisplayAlerts = False   

For Each ws In ThisWorkbook.Sheets
     If InStr(1, ws.Name, TexttoFind, vbBinaryCompare) <> 0 Then
         ws.Copy
         Application.ActiveWorkbook.SaveAs Filename:=FPath & "" & ws.Name & ".xlsx"
         Application.ActiveWorkbook.Close False
     End If 
Next   

Application.DisplayAlerts = True 
Application.ScreenUpdating = True 
End Sub  

Как вы могли заметить, прямо в коде, мы создали переменную и задали ей значение 2020.

Затем этот код просто проходится по каждой странице и проверяет есть ли в имени нужная нам часть (то есть нужный год). А далее сохраняет отдельно только те листы, в имени которых он нашел совпадения.

Если совпадения не будут найдены — результат будет 0.

В этом коде используется цикл «Если/То». Если он находит нужное текстовое значение в имени листа, то сохраняет его отдельно, если не находит — просто пропускает.