Листы в Excel из списка по шаблону

Сегодня я покажу, как заполнить однотипные листы, по шаблону Excel на основе списка.

Смотрите видео: Листы в Excel из списка по шаблону

Переименуем в книге первый лист, это у нас будет список, допустим сотрудников.

Второй лист назовём шаблон, в нем будет находиться таблица, которую сотрудники будут заполнять либо вы будите заполнять для данных сотрудников из списка.

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

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

Сохраним наш документ как книга Excel с поддержкой макросов с расширением так xlsm, на ленте должен находиться в последнем пункте - пункт Разработчик, если его нет, то в файл, параметры нужно будет в настройках ленты добавить этот пункт, отметьте его галочкой.

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

Перейдём на вкладке Разработчик в Visual Basic либо нажмем Alt+F11, для того чтобы открылся редактор макросов.

Нажмем в меню insert – module, напишем модуль Sub ListTempl(), объявим переменную tmpName.

Получим данные нашего списка из листа список в переменную tmpName, объявив диапазон А1:А3. Но если нужно будет больше, то будем менять это значение. Пока в примере оставим так.

Напишем цикл for от 1 до 3, по сколько мы знаем конечное количество записей нашего списка и теперь сделаем копию листа шаблон, создадим новый лист копии листа с шаблоном, шаблон и присвоим имя каждому листу, значение то которое у нас находится в списке. Закончим цикл next i.

Теперь нажмем правой кнопкой на кнопке, назначить макрос, выберем макрос ListTempl, OK, сохранить.

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

Sub ListTempl()
Dim tmpName
tmpName = Sheets("Шаблон").Range("a1:a3")

For i = 1 To 3
Sheets("Шаблон").Copy Before:=Sheets(i)
Sheets(i).Name = tmpName(i, 1)
Next i

End Sub

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

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

Давайте добавим теперь в шапку шаблона формулу, которая будет в ячейку присваивать имя листа, сейчас формула находится на листе с названием шаблон.

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

=ПРОПНАЧ(ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("]";ЯЧЕЙКА("имяфайла";A1))+1;99))

Давайте допишет для примера как это бывает в таблицах, «Список для », а далее будет фамилия поставляться из названия листа.

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

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

Если же всё-таки вам нужно будет сделать два листа для одной уникальной записи, тут могу предложить следующее.

Изменим в коде диапазон на А4, так как у нас ещё один пункт добавился и в цикле исправим на четыре.

Sub ListTempl()
Dim tmpName
tmpName = Sheets("Шаблон").Range("a1:a4")

For i = 1 To 4
Sheets("Шаблон").Copy Before:=Sheets(i)
Sheets(i).Name = tmpName(i, 1)
Next i

End Sub

Удалим всё снова и нажмем на кнопку. Опять ошибка, как вариант предлагаю добавить пробел в конце названия дублируемого пункта списка.

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

Смотрите видео: Листы в Excel из списка по шаблону

Понравилась статья? Поделитесь ею с друзьями и напишите отзыв в комментариях!