Как сохранить лист из книги excel

Как сохранить листы рабочей книги Excel отдельными файлами?

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

Традиционно предложу два способа позволяющих перемещать листы в отдельные файлы Excel.

Как сохранить лист отдельным файлом стандартными средствами Excel?

Первый — стандартными средставми приложения Excel. Кликаем правой кнопкой мышки в области наименований листов и выбираем пункт меню «Переместить/скопировать. «

В поле «Переместить выбранные листы в книгу» выбираем либо «(новая книга)», либо выбираем название файла, в который собираемся перемещать лист (этот файл должен быть предварительно открыт). Жмем «Ок» — лист перемещается. Да, если отметить галочкой опцию «Создать копию», то вместо перемещения листа произойдет его копирование, а также можно будет указать место для вставки листа.

Как сохранить листы отдельными файлами при помощи надстройки?

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

Использование надстройки позволяет:

1. Вызывать диалоговое окно надстройки одним кликом мыши прямо из вкладки на ленте;

2. выбирать папку, в которую будут сохраняться рабочие книги;

3. при делении рабочей книги на отдельные файлы назвать новые файлы именами листов;

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

5. создавать уникальные имена файлов, добавляя текущую дату и время к именам файлов;

6. выбирать наиболее подходящее расширение (тип) для новых рабочих книг.

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

Сохранение нескольких листов в Excel

Добрый день, уважаемые читатели и подписчики блога. Сегодня мы поговорим о сохранении листов большой книги Excel. Материал о расчётах между листами и книгами в Excel.

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

Способ №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

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

Читать еще: 700 столбец в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Разнесение данных на разные листы/книги

Данная функция является частью надстройки MulTEx

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

Вызов команды:
MulTEx -группа Книги/Листы —Книги —Разнесение данных

Команда, делающее обратное Сбору данных с нескольких листов/книг — разносит данные листа на несколько других листов либо книг. Количество листов/книг зависит от количества уникальных значений критериев для разнесения. Листы и Книги создаются при выполнении команды. Для чего это может быть нужно? Например, есть график отпусков сотрудников, который надо разбить по фамилии руководителя отдела и каждому руководителю отправить по почте список только его сотрудников:

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

Основные

Разнести:

Все данные — при выборе будет произведено разнесение абсолютно всех данных, расположенных на листе.
Указанный диапазон — будут разнесены только те данные, которые расположены внутри указанного диапазона. Диапазон следует указывать без заголовка. Иначе заголовок будет определен как отдельный критерий и для него будет так же создан свой лист/книга.

Читать еще: Текст в число excel формула

Критерии:

На основании значений — разнесение данных происходит на основании значений в ячейках. На рисунках выше таблица разносится именно на основании значений и в качестве столбца с критериями указан столбец с фамилиями руководителей. Это значит, что после выполнения команды будет создана новая книга, содержащая ровно столько листов, сколько руководителей в таблице. И в каждом листе будут содержаться только те сотрудники, которые работают в отделе, подчиненном данному руководителю.
На основании цвета заливки — разнесение данных происходит на основании цвета заливки ячеек. В данном случае новым листам/книгам будет присвоено имя, равное числовому коду цвета заливки ячеек, а цвет ярлыков листов — равен цвету заливки ячеек.
На основании цвета шрифта — разнесение данных происходит на основании цвета шрифта. В данном случае новым листам/книгам будет присвоено имя, равное числовому коду цвета шрифта, а цвет ярлыков листов — равен цвету шрифта.
На фиксированное количество строк — данные будут разнесены пропорционально указанному количеству строк в окне напротив данной опции. Будут созданы листы/книги, количество строк с данными в которых будет равно указанному. При этом имена книг и листов будут называться диапазонами строк. Например, если выбрать разбиение диапазона по 10 строк, то результирующие листы или книги будут названы: 1-10 , 11-20 , 21-30 и т.д.

Поместить:

На разные листы одной книги

Примечание: если в значении критерия имеются символы, недопустимые в имени листа( :/?*[] ), то эти символы удаляются. Если критерий содержит исключительно запрещенные символы, то они все будут удалены, а лист, на который будут помещены такие данные будет назван «_invalid_chars_» .
Если количество символов в значении превышает 30(максимально допустимое количество символов в имени листа — 31), то значение обрезается до 30 символов.

Примечание: если в значении критерия имеются символы, недопустимые в имени книги( :/?*»<>| ), то эти символы удаляются. Если критерий содержит исключительно запрещенные символы, то они все будут удалены, а книга, на которую будут помещены такие данные будет названа «_invalid_chars_».
Если количество символов в значении превышает 30, то значение обрезается до 30 символов(для большей «удобочитаемости», а так же для предотвращения ошибки, возникающей при длине пути к файлу, превышающей корректное определение файла операционной системой).

Номер столбца с критериями разнесения — указывается номер столбца, в котором расположены значения критериев для разнесения. Если на вкладке Основные выбрано Все данные — указывается номер столбца на листе. Т.е. если таблица данных расположена в диапазоне C3:G20 и критерии расположены в столбце D , то следует указать номер столбца 4 . Если на вкладке Основные выбрано Указанный диапазон, то указывается номер столбца внутри выбранного диапазона. Т.е. если указан диапазон C3:G20 и критерии расположены в столбце D , то следует указать номер столбца 2 .

Копировать только значения и форматы — в новые листы/книги будут скопированы только значения и форматы ячеек. Формулы будут заменены на значения, что позволяет избежать ошибочных значений #ССЫЛКА! (#REF!) при копировании формул со ссылками на другие листы.

Копировать заголовок на каждый лист — в новые листы/книги будет скопирован диапазон ячеек, указанный в поле. Диапазон для заголовков может быть расположен на любом листе любой открытой книги, а не обязательно на листе со значениями для разнесения. Рекомендуется указывать диапазон, ячейки самой нижней строки которого заполнены полностью. Это необходимо для корректного определения конца заголовка программой при вставке строк данных на лист.

Отправка
Если после разбиения данных на листы книги, необходимо отправлять созданные «нарезки» определенным адресатам — необходимо выбрать параметры рассылки на этой вкладке. Если по каким-то причинам какие-то данные не получится отправить — то будет создано сообщение, информирующее об этом.

Отправлять создаваемые листы/книги — если установлен, то созданные листы/книги будут отправлены на указанные адреса e-mail. Адреса могут быть указаны как в самой таблице для разнесения, так и отдельным списком соответствия.

Брать адреса e-mail из столбца — указывается номер столбца. Если на вкладке Основные выбрано Все данные, то указывается номер столбца на листе, даже если сами данные для разнесения начинаются с 3 или любого другого столбца. Т.е. если таблица данных расположена в диапазоне C3:G20 и адреса e-mail при этом расположены в столбце G , то следует указать номер столбца 7 . Если на вкладке Основные выбрано Указанный диапазон, то указывается номер столбца внутри выбранного диапазона. Т.е. если указан диапазон C3:G20 и адреса e-mail при этом расположены в столбце G , то следует указать номер столбца 5 .
В данном случае необходимо, чтобы в самой таблице для разнесения присутствовал столбец с корректными адресами e-mail, на которые необходимо отправлять созданные файлы. При этом необязательно указывать e-mail для каждой строки — достаточно, если e-mail будет записан один раз для каждого критерия.
Адреса по списку соответствия — при выборе данного пункта необходимо заранее подготовить список соответствия адресов e-mail критериям в таблице. На примере таблицы выше список может выглядеть так:

При этом критерии и сами адреса e-mail должны располагаться в двух смежных столбцах: слева критерии, справа — адреса. На примере таблиц выше это столбцы B и C. Т.к. в качестве критерия разнесения на вкладке Основные выбраны были ФИО руководителя из столбца Руководитель, то в качестве списка соответствия необходимо указать диапазон B2:C5 .

Читать еще: Как установить параметры страницы в excel

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

Отправлять средствами Excel — файлы будут отправлены почтовой программой, установленной по умолчанию. Аналогично стандартной отправке листов и книг из самого Excel:
- Excel 2007 : Кнопка Офис —Отправить (Send) —Сообщение (E-mail) —Как вложение (Send as attachment)
- Excel 2010 : Файл (File) —Сохранить и отправить (Save & Send) —Отправить (Send Using E-mail) —Как вложение (Send as attachment)
Отправлять через Outlook — файлы будут отправлены при помощи почтовой программы Outlook. Будет создано стандартное сообщение с темой и в каждое сообщение будет вложен свой файл.
Отправлять через CDO — отправка файлов данным методом не требует наличия почтовой программы на компьютере. Отправка производится при помощи CDO(Collaboration Data Objects) — библиотеки, встроенной в операционную систему Windows. Для того, чтобы её использовать необходимо лишь знать настройки почтового сервера(Яндекс, Рамблер, Mail.ru и др.).