Как сделать несколько копий листа в excel

Обновлено: 05.07.2024

Если у вас есть книга 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:

Затем код VBA использует цикл For Next в VBA для просмотра каждого рабочего листа, а затем проверяет имя каждой функции INSTR рабочего листа. Эта функция проверяет, есть ли в имени листа слово 2020 или нет. Если это так, он вернет номер позиции, в которой он нашел этот текст (в данном случае это 2020).

И если он не находит искомый текст, он возвращает 0.

Это используется с условием IF Then. Поэтому, если в имени листа есть текстовая строка 2020, оно будет разделено и сохранено как отдельный файл. И если у него нет этой текстовой строки, условие IF не будет выполнено, и ничего не произойдет.

Как быстро скопировать лист Excel? Как переместить лист в другую книгу?

Отмечу, ничего дурного, в том чтобы пользоваться копировать/вставить для дублирования листов нет. Хотя вы можете скопировать таким образом не все данные, например, не все объекты. Знать другие удобные способы копирования и перемещения тоже будет полезно

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

Предположим у вас есть лист с данными. На ярлычке листа кликните правой кнопкой мыши и выберите Переместить или скопировать

В открывшемся диалоговом окне выберите то, что вам необходимо от программы

Отметив галочку Создать копию после нажатия ОК, вы получите копию листа в нужном месте, а в разделе перед листом как раз нужно выбрать это место.

Если надо выделить все листы между разными листами, выберите 1 лист, зажмите Shift и выберите последний лист.

Как переместить лист в другую книгу?

Чтобы переместить лист в другую книгу, есть специальный раздел Переместить выбранные листы в книгу

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

Важно! Если вы перемещаете или копируете лист в другую книгу, то если на листе были формулы они не будет работать в новой книге, если закрыть изначальный файл.

Копирование листов при помощи VBA

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

Если надо копировать листы в другую книгу, то выглядеть это должно примерно так

Как обычно, способов несколько.

Способ №1 (стандартный)

Щёлкаем правой кнопкой мышки на имя листа.

Выбираем команду "Переместить или скопировать".

В поле "Переместить выбранные листы в книгу:" выбираем "Новая книга".

После нажатия "ОК" произойдёт создание новой книги и выгрузка туда выделенного листа. Останется только сохранить документ.

Если нужно сохранить два и более листов их нужно выделить. Зажав клавишу Ctrl щёлкнуть на имя листа, затем повторить все вышеописанные действия.

Способ № 2 (макрос)

Представим ситуацию когда нужно одну большую книгу разделить на отдельные файлы. Сохранить требуется только выделенные листы.

Добавим новый модуль в нашу книгу:

На вкладке "Разработчик" нажимаем Visual Basic;
далее Insert - > Module;
записываем макрос.

Sub SohrList()
Dim CurrentWin As Window
Dim VremWin As Window
Set CurrentWin = ActiveWindow
Set VremWin = ActiveWorkbook.NewWindow
CurrentWin.SelectedSheets.Copy
VremWin.Close
End Sub

В итоге выделенные листы перейдут в новую книгу. Останется только её сохранить.

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

Sub razbkn()
Dim q As Worksheet
Dim rabkn As Workbook
Set rabkn = ActiveWorkbook
For Each q In rabkniga.Worksheets
q.Copy
ActiveWorkbook.SaveAs rabkn.Path & "\" & q.Name & ".xlsx"
End Sub

Немного пояснений. Этот макрос лучше использовать в том случае, когда книга уже находится в отдельной папке. Все извлечённые из неё литы будут сохранены в той же папке, что и документ родитель. Не стоит использовать макрос для вновь созданной, но ещё не сохранённой книги - программа будет просто вылетать.

Если знаете ещё способы - делитесь ими в комментариях, буду рад научиться чему-нибудь новому! Всем удачи!

При создании документа в Microsoft Excel в старых версиях открывается 3 листа, в новых – 1. Между ними можно переключаться и заполнять их независимо друг от друга. Можно создавать связи и решать сложные задачи, передавая между ними данные. Возможность форматирования и программирования ячеек дополнительно расширяют функционал. Если стандартного запаса не хватает, то мы расскажем, как вставить лист в Excel.

Теперь о том, как добавить лист в Excel через вставку.

В Экселе 2010 делаем иначе.

Такое добавление расположит новый документ в начале списка.

Как вставить новый чистый лист в книгу Excel?

Добавление листа из контекстного меню

Для того чтобы добавить раздел из контекстного меню:

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

В Excel 2010 вы увидите другое меню, поэтому действовать нужно немного по-другому:

Новый раздел также будет создан перед выбранным. Этим способом можно решить, как в Экселе добавить лист в конкретное место списка.

Как скрыть лист в Excel?

Если у вас сложилась ситуация, которая вынуждает скрыть лист в Excel, то можно не волноваться, есть такая возможность. Разработчики предоставили такую функцию. Скрытые листы в Excel 2010 по-прежнему будут находиться в книге, на них могут храниться формулы и другая информация, но они не будут отображаться. Как скрыть листы Excel 2010:

Жмём правой кнопкой мыши на нужном листе, чтобы вызвать контекстное меню
Выбираем пункт “Скрыть”

В версиях ниже 2003 скрытие происходит иначе, так как контекстное меню в них было недоступным. Для скрытия в таких версиях жмите “Формат” -“Лист” -“Скрыть”.

Есть нюанс. Лист не получится скрыть, если он является единственным листом в книге.

Вам помогли наши простые методы? Рассказывайте в комментариях ниже.

Листы Excel как среда для работы с данными

В стандартной книге каждый лист представляет собой большие таблицы, которые заполняются данными и формулами. Как добавить лист в Excel? По мере необходимости можно вставлять дополнительные листы (SHIFT+F11) или удалять неиспользуемые. Сделать новый лист в Excel, можно кликнув по последней закладке листов, как показано на рисунке:

Книга может содержать от 1-го до 255 листов.

Как скопировать лист в Excel? Для быстрой реализации данной задачи необходимо:

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

Если листов очень много или необходимо скопировать / переместить лист в новую книгу тогда выполняем следующие действия:

Способ четвертый: нумерация с определенного листа

Итак, если вы хотите начать нумерацию с определенного листа, вам необходимо:

В данной статье мы рассмотрим с вами как можно пронумеровать страницы в excel 2010. Если у вас Excel 2003, то читайте статью: как пронумеровать страницы в Excel 2003

Как создать множество копий листа? Как добавить в книгу листы с именами из ячеек

При работе с файлами, содержащими множество листов, либо при необходимости периодически тиражировать, делать множество копий заданного листа, например, при создании квитанций в Excel, альтернативой стандартному, ручному добавлению или копированию листов может служить макрос (надстройка)

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

Для того, чтобы воспользоваться этим средством, необходимо установить надстройку на свой компьютер ,

вызвать диалоговое окно надстройки и задать в нём исходные данные для работы программы.

видео по работе с надстройкой

надстройка (макрос) для добавления новых листов и копирования существующих

Можно выбрать одну из доступных опций:

Вставить новые листы в заданном количестве

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

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

Присвоить новым листам именами из ячеек

По умолчанию, документ Excel состоит из трех листов, но при необходимости пользователь может добавить любое количество новых листов. В данной статье мы рассмотрим сразу 4 способа добавления листов в Excel, а также расскажем о решении проблемы с исчезнувшими листами. Статья будет полезной для всех современных версий Excel, включая Excel 2003, 2010, 2013 и 2020.

Способ № 1. Кнопка для добавления листа.

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

Способ № 3. Комбинация клавиш Shift-F11.

Еще можно новые листы можно добавлять с помощью комбинации клавиш Shift-F11. Просто нажимаете Shift-F11 в любой удобный вам момент и получаете новый лист.

Способ № 4. Добавление листа из контекстного меню.

В Excel нет листов, что делать?

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

В Excel 2003

В Excel 2007

В Excel 2010, 2013, 2016

После этого панель листов в нижней левой части должна появиться.

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

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

Работа с листами

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

,
Лист 2
и
Лист 3
. Чтобы систематизировать информацию в книге и облегчить навигацию, вы можете переименовать и даже изменить цвет ярлыка листов. Также вы можете добавить, удалить, передвинуть и скопировать листы.

Чтобы переименовать лист:

Чтобы создать новый лист:

Кликните по иконке Вставить лист. Появится новый лист.

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

Чтобы удалить лист:

Листы могут быть удалены из книги, даже если они содержат данные.

Выберите лист, который нужно удалить.
Кликните правой кнопкой мыши по ярлыку листа. Появится контекстное меню.
Выберите Удалить. Выбранный лист будет удален из книги.

Чтобы скопировать лист:

Чтобы переместить лист:

Чтобы перекрасить ярлык листа:

Вы можете перекрасить ярлычки листов, чтобы систематизировать информацию и облегчить навигацию по книге.

Группировка и разгруппировка листов

Вы можете работать с каждым листом отдельно или с несколькими листами одновременно. Листы могут быть объединены в группу. Любые изменения, сделанные с листом в группе, будут применены и к остальным листам этой группы.

Чтобы сгруппировать листы:

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

Чтобы разгруппировать все листы:

Кликните правой кнопкой по ярлычкам листов. Появится контекстное меню.
Выберите Разгруппировать. Листы будут разгруппированы.

Закрепление областей листа

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

Читайте также: