Подивіться відео - Як розділити кожен аркуш Excel на окремі файли
Якщо у вас є книга Excel із багатьма аркушами, немає простого способу розділити кожен з цих аркушів на окремі файли Excel та зберегти окремо.
Це може знадобитися, коли ви складаєте аркуші для різних місяців або регіонів або продуктів/клієнтів, і ви хочете швидко отримати окрему книгу для кожного аркуша (у вигляді файлу Excel або у форматі PDF).
Хоча існує спосіб вручну розділити аркуші на окремі книги, а потім зберегти їх, це неефективно та схильно до помилок.
У цьому посібнику я дам вам простий код VBA, за допомогою якого можна швидко (за кілька секунд) розділити всі аркуші на окремі файли, а потім зберегти їх у будь -якій зазначеній папці.
Розділіть кожен аркуш на окремий файл Excel
Припустимо, у вас є робоча книга, як показано нижче, де у вас є робочий аркуш на кожен місяць.
Щоб розділити ці аркуші в окремий файл Excel, можна скористатися наведеним нижче кодом VBA:
'Код, створений Sumit Bansal з trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False Для кожного ws у ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs Ім'я файлу: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Перш ніж використовувати вищевказаний код VBA, вам потрібно переконатися в кількох речах:
- Створіть папку, куди ви хочете отримати всі отримані файли.
- Збережіть основний файл Excel (який містить усі потрібні вам аркуші як окремі файли) у цій папці.
Як тільки ви це зробите, ви можете помістити вищевказаний код VBA у файл і запустити код.
Наведений вище код написано таким чином, що він збирає розташування папки, використовуючи шлях до файлу (у якому виконується код). Ось чому важливо спочатку зберегти файл у папці, а потім використовувати цей код.
Як працює код VBA - У наведеному вище коді використовується простий цикл For Next, який проходить кожен робочий аркуш, створює копію робочого аркуша у книзі Excel, а потім зберігає цю книгу Excel у зазначеній папці (яка є тією самою, у якій є основний файл із усіма аркушів).
Нижче наведено кроки для розміщення цього коду VBA у книзі Excel (вони будуть однаковими для всіх інших методів, показаних у цьому підручнику):
Де розмістити цей код?
Нижче наведено кроки для розміщення коду у редакторі Visual Basic, де його можна виконати:
- Перейдіть на вкладку Розробник.
- У групі Код натисніть на опцію Visual Basic. Відкриється редактор VB. [Ви також можете використовувати комбінацію клавіш - ALT + F11]
- У редакторі VB клацніть правою кнопкою миші будь-який об’єкт робочої книги, над яким ви працюєте.
- Наведіть курсор на опцію Вставка
- Натисніть на модуль. Це додасть новий модуль
- Двічі клацніть на об'єкті Module. це відкриє вікно коду для модуля
- Скопіюйте наведений вище код VBA і вставте його у вікно коду модуля.
- Виберіть будь -який рядок у коді та натисніть зелену кнопку відтворення на панелі інструментів, щоб запустити код макросу VBA.
Вищеописані кроки миттєво розділили б робочі листи на окремі файли Excel та зберегли їх. Це займає лише секунду, якщо у вас менше робочих аркушів. Якщо у вас багато, це може зайняти деякий час.
Ім’я кожного збереженого файлу таке ж, як і ім’я аркуша, яке було в основному файлі.
Оскільки ви розмістили код VBA у книзі Excel, вам потрібно зберегти його у форматі .XLSM (це формат з підтримкою макросів). Це забезпечить збереження та роботу макросу, коли ви відкриєте цей файл наступного разу.
Зауважте, що я використав рядки Application.ScreenUpdating = Неправда та Application.DisplayAlerts = Неправда у коді, щоб все відбувалося у бекенді та не бачило, що відбувається на екрані. Як тільки код запуститься, розділить аркуші та збереже їх, ми повернемо їх назад у ІСТИНУ.
В якості найкращої практики рекомендується створити резервну копію основного файлу (який містить аркуші, які потрібно розділити). Це гарантує, що ви не втратите свої дані, якщо щось піде не так або якщо Excel вирішить уповільнитися або вийти з ладу.
Розділіть кожен аркуш і збережіть його як окремі PDF -файли
Якщо ви хочете розділити аркуші та зберегти їх у форматі PDF замість файлів Excel, ви можете скористатися наведеним нижче кодом:
'Код, створений Sumit Bansal з trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False Для кожного ws у ThisWorkbook.Sheets ws.Copy Application.ActiveSheet.Export Тип: = xlTypePDF, Ім'я файлу: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Що потрібно переконатися перед використанням цього коду:
- Створіть папку, куди ви хочете отримати всі отримані файли.
- Збережіть основний файл Excel (який містить усі потрібні вам аркуші як окремі файли) у цій папці.
Наведений вище код розбиває кожен аркуш у файлі Excel і зберігає його як PDF у тій же папці, де ви зберегли основний файл Excel.
Розділіть лише ті робочі аркуші, які містять слово/фразу, на окремі файли Excel
Якщо у вас є багато аркушів у книзі, і ви хочете розділити лише ті аркуші, у яких є певний текст, ви також можете це зробити.
Наприклад, припустимо, що у вас є файл Excel, де ви зберігаєте дані протягом кількох років, і кожен аркуш у файлі має префікс номер року. Щось, як показано нижче:
Припустимо, ви хочете розділити всі аркуші на 2021-2022 роки та зберегти їх як окремі файли Excel. Для цього вам потрібно якось перевірити назву кожного робочого аркуша, і лише ті аркуші, які мають номер 2021-2022, слід розділити та зберегти, а решту залишити недоторканою.
Це можна зробити за допомогою такого коду макросу VBA:
'Код, створений Sumit Bansal з trumpexcel.com Sub SplitEachWorksheet () Затемнення FPath як рядка Dim Dim TexttoFind As String TexttoFind = "2020" FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False Для кожного ws у цій книзі. Аркуші If InStr (1, ws.Name, TexttoFind, vbBinaryCompare) 0 Тоді ws.Copy Application.ActiveWorkbook.SaveAs Ім'я файлу: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = Справжня програма.ScreenUpdating = Справжня кінцева підп
У наведеному вище коді я використав змінну TexttoFind, яка на початку була призначена “2020”.
Код VBA потім використовує цикл For Next у VBA для проходження кожного аркуша, а потім перевіряє ім’я кожної функції INSTR на аркуші. Ця функція перевіряє, чи містить у назві аркуша слово 2021-2022 чи ні. Якщо це станеться, він поверне номер позиції, де він знайде цей текст (який у цьому випадку становить 2021-2022).
І якщо він не знаходить текст, який ми шукаємо, він повертає 0.
Це використовується з умовою IF Тоді. Отже, якщо у назві аркуша є текстовий рядок2021-2022, його буде розбито та збережено як окремий файл. І якщо у ньому немає цього текстового рядка, умова IF не буде виконана, і нічого не станеться.
