Vba добавить лист с именем: Как добавить новый лист и присвоить имя в Excel пошаговое руководство

Макрос для копирования и переименования листов Excel

В этом примере детально описана и разобрана автоматизация копирования и переименования листов Excel с исходными кодами макросов.

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

Представьте ситуацию: Вы готовите планы работ для сотрудников определенного отдела фирмы. Пример таблицы плана выглядит так:

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

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

Техническое Задание (ТЗ) для макроса заключается в следующем. После выделения ячеек, которые содержат названия для листов нужно скопировать лист с шаблоном для планов работ ровно столько сколько будет выделенных ячеек и ввести соответствующие названия в ярлычки.

Примечание. В программировании, как и в многих других технических сферах деятельности (например, в бизнес-планировании) – без ТЗ результат ХЗ! Автор цитаты известный бизнесмен и предприниматель Дмитрий Потапенко.

Для этого сначала откройте редактор кода макроса: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» (ALT+F11):

В редакторе создайте новый модуль выбрав инструмент «Insert»-«Module» и введите в него следующий код макроса:

Sub PlanRabot()
  Dim diapaz As Range
  Dim i As Long
  Dim list As Worksheet
On Error Resume Next
Set diapaz = Application.InputBox("Пожалуйста, выделите диапазон ячеек, который содержит названия для новых листов!", Type:=8)
On Error GoTo 0
If diapaz Is Nothing Then Exit Sub
Set list = ActiveSheet
For i = 1 To diapaz.Count
list.Copy after:=ActiveSheet
ActiveSheet.Name = Left(diapaz(i), 31)
Next
End Sub

Теперь если вы хотите скопировать лист шаблона для заполнения плана работ и создать копии плана для каждого сотрудника имена с фамилиями которых будут присвоены названиям листа, то перейдите на исходный лист с шаблоном плана работ и выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«PlanRabot»-«Выполнить». Сразу же после запуска макроса появиться диалоговое окно:

Теперь перейдите на лист «Имена и Фамилии» и выделите в нем диапазон ячеек, которые содержат имена и фамилии сотрудников. И нажмите на кнопку ОК.

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



Описание исходного кода макроса для копирования и переименования листов

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

Перед вызовом инструкции открытия диалогового окна отключается контроль ошибок. А после создания экземпляра объекта Range в переменной diapaz, снова включается контроль ошибок. Сделано так потому, что нажатие на кнопку «Отмена» в диалоговом окне вызовет ошибку выполнения макроса. Если же отключить обработку ошибок то, когда пользователь нажмет на кнопку «Отмена», тогда просто не будут выполняться никакие инструкции макроса.

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

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

Если необходимо сделать так чтобы соответственные имена и фамилии сотрудников были не только на ярлычках листов, а и в значении ячейки B1 в каждом листе, тогда перед строкой конца цикла Next добавьте следующую строку кода:

ActiveSheet.Range(«B1») = diapaz(i)

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

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

Sub PlanRabot()
  Dim diapaz As Range
  Dim i As Long
  Dim list As Worksheet
On Error Resume Next
Set diapaz = Application. InputBox("Пожалуйста, выделите диапазон ячеек, который содержит названия для новых листов!", Type:=8)
On Error GoTo 0
If diapaz Is Nothing Then Exit Sub
Set list = ActiveSheet
For i = 1 To diapaz.Count
list.Copy after:=ActiveSheet
ActiveSheet.Name = Left(diapaz(i), 31)
ActiveSheet.Range("B1") = diapaz(i)
Next
End Sub

Пример вставки имен и фамилий не только в ярлычки листов, а и в их определенные пользователем ячейки:

Читайте также: Макрос для копирования листов Excel с заданным количеством копий.

Таким образом, благодаря макросу в пару кликов мышкой можно выполнить большой объем работы. В данном примере скопировано копий только для 8-ми сотрудников. Особенно ощутимую пользу приносит данный макрос при необходимости копировать большое количество листов. Ведь в некоторых фирмах количество сотрудников, заполняющих планы работы, может превышать 100 человек и более.

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

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

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

Для вставки нового листа достаточно выбрать пункт меню «Вставить…», либо нажать кнопку, следующую сразу за последним листом рабочей книги. Еще одним способом добавления нового листа в рабочую книгу является нажатие сочетания клавиш Shift+F11 на клавиатуре. Можно также настроить создание книг с произвольным количеством листов, если Вас не устраивают стандартные три листа. Сделать это можно следующим образом, нажать кнопку Office, затем в меню выбрать «Параметры Excel» и в разделе «Основные» выбрать группу «При создании книг». В графе «Число листов» задайте нужное значение.

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

Для того чтобы сделать копию листа в Excel 2007, необходимо выбрать лист, который Вы хотите скопировать и кликнуть на нем правой кнопкой мыши, выбрать пункт «Переместить/скопировать…».

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

 

Как изменить имя или переименовать лист рабочей книги Excel?

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

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

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

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

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

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

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

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

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

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

2. Скопировать активный лист заданное число раз

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

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

Вместо того чтобы создавать новые листы со стандартными именами «Лист1», «Лист2», «Лист3» и так далее, а затем переименовывать их, тратя на эту операцию много времени, можно предварительно сформировать список имен в ячейках листа рабочей книги, а затем в диалоговом окне надстройки выделить диапазон с нужными именами и запустить программу. Перед созданием листов с именами из значений ячеек производится проверка этих значений на соответствие требованиям к именам листов, которые:

а) не должны превышать 31 знака;

б) не должны содержать знаков: : \ / ? * [ или ];

в) не должны оставаться пустыми.

После прохождения проверки добавляемым листам присваиваются имена в соответствии со значениями ячеек выделенного диапазона.

Создание квитанций в Excel

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

Другие материалы по теме:

vba — Как добавить именованный лист в конец всех листов Excel?

спросил

Изменено
4 года, 3 месяца назад

Просмотрено
708 тысяч раз

Я пытаюсь добавить лист Excel с именем «Temp» в конец всех существующих листов, но этот код не работает:

 Частный подчиненный CreateSheet()
    Dim ws As рабочий лист
    ws.Name = "Темп"
    Установите ws = Sheets.Add(After:=Sheets(Sheets.Count))
Конец сабвуфера

Не могли бы вы сообщить мне почему?

  • Excel
  • vba
  • рабочий лист

1

Попробуйте это:

 Private Sub CreateSheet()
    Dim ws As рабочий лист
    Установите ws = ThisWorkbook. Sheets.Add (после: = _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "Темп"
Конец сабвуфера

Или используйте предложение With , чтобы избежать повторного вызова вашего объекта

 Private Sub CreateSheet()
    Dim ws As рабочий лист
    С этой рабочей тетрадью
        Установите ws = .Sheets.Add(После:=.Sheets(.Sheets.Count))
        ws.Name = "Темп"
    Конец с
Конец сабвуфера

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

 Sub CreateSheet()
    С этой рабочей тетрадью
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
    Конец с
Конец сабвуфера

5

Пожалуйста, используйте этот вкладыш:

 Sheets.Add(After:=Sheets(Sheets.Count)).Name = "new_sheet_name"

2

 ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "XYZ"

(при добавлении рабочего листа он все равно будет активным)

Попробуйте это:

 Public Enum iSide
iДо
iПосле
Конец перечисления
Частная функция addSheet (ByRef inWB As Workbook, ByVal inBeforeOrAfter As iSide, ByRef inNamePrefix As String, ByVal inName As String) As Worksheet
    При ошибке GoTo the_dark
    Dim wsSheet как рабочий лист
    Dim bFoundWS как логическое значение
    bFoundWS = Ложь
    Если inNamePrefix <> "" Тогда
        Установите wsSheet = findWS (inWB, inNamePrefix, bFoundWS)
    Конец, если
    Если inBeforeOrAfter = iAfter Тогда
        Если wsSheet ничего не значит или bFoundWS = False, тогда
            Worksheets. Add(After:=Worksheets(Worksheets.Count)).Name = inName
        Еще
            Листы.Добавить(После:=wsSheet).Name = inName
        Конец, если
    Еще
        Если wsSheet ничего не значит или bFoundWS = False, тогда
            Worksheets.Add(Before:=Worksheets(1)).Name = inName
        Еще
            Worksheets.Add(Before:=wsSheet).Name = inName
        Конец, если
    Конец, если
    Установите addSheet = findWS(inWB, inName, bFoundWS) ' просто чтобы подтвердить его существование и получить его обработку
    свет:
    Выход из функции
    the_dark:
    MsgBox "addSheet: " & inName & ": " & Err.Description, vbOKOnly, "непредвиденная ошибка"
    Очистить
    Перейти к the_light
Конечная функция

Попробуйте использовать:

 Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"

Если вы хотите проверить, существует ли уже лист с таким именем, вы можете создать функцию:

 Функция funcCreateList(argCreateList)
    Для каждого рабочего листа в ThisWorkbook. Worksheets
        Если argCreateList = Рабочий Лист.Имя Тогда
            Exit Function ', если найдено - выйти из функции
        Конец, если
    Следующий рабочий лист
    Рабочие листы.Добавить (после:=Рабочие листы(Рабочие листы.Количество)).Имя = argCreateList
Конечная функция

Когда функция создана, вы можете вызвать ее из вашего основного Sub, например:

 Sub main
    funcCreateList "Мой лист"
Выйти из подпрограммы

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

 Частная подпрограмма CreateSheet()
    Dim ws As рабочий лист
    Установите ws = Sheets.Add(After:=Sheets(Sheets.Count))
    ws.Name = "Темп"
Конец сабвуфера

спасибо,

Это даст вам возможность:

  1. Перезаписать или сохранить вкладку с тем же именем.
  2. Поместите лист в конец всех вкладок или рядом с текущей вкладкой.
  3. Выберите новый лист или активный.
 Вызов CreateWorksheet("Новый", Ложь, Ложь, Ложь)
Sub CreateWorksheet (имя листа, saveOldSheet, isLastSheet, selectActiveSheet)
  activeSheetNumber = Листы (ActiveSheet. Name).Index
  If (Evaluate("ISREF('" & sheetName & "'!A1)")) Then 'Существует ли лист?
    Если (сохранитьСтарый Лист) Тогда
      MsgBox ("Невозможно создать лист " + имя_листа + ". Этот лист существует.")
      Выйти из подпрограммы
    Конец, если
      Application.DisplayAlerts = Ложь
      Рабочие листы(имя листа).Удалить
    Конец, если
    Если (Последний Лист) Тогда
      Sheets.Add(After:=Sheets(Sheets.Count)).Name = sheetName 'Поместить лист в конец.
    Else 'Поместить лист после активного листа.
      Листы.Добавить(После:=Листы(activeSheetNumber)).Имя = имялиста
    Конец, если
    Если (selectActiveSheet) Тогда
      Листы(activeSheetNumber).Активировать
    Конец, если
Конец сабвуфера

Это быстрое и простое добавление именованной вкладки на текущий рабочий лист:

 Sheets.Add.Name = "Tempo"

excel — VBA Добавить новый лист, если имя существует, добавить номер

спросил

Изменено
3 года, 2 месяца назад

Просмотрено
3к раз

следующий код проверяет, существует ли лист с именем «Окончательный» , если да, он создает еще один рабочий лист, но имя зависит от количества листов в рабочей книге. Таким образом, если имеется только один лист с именем "Final_12" и 10 разных листов (всего 11 листов), макрос добавит новый лист с именем "Final_12" .
Как изменить код, чтобы он создавал "Final_1" , "Final_2" и т.д. листы?

 Установить WSF = wb.Worksheets.Add (После: = wb.Worksheets (PayrollWS))
Установить NewSht = ActiveSheet
новоеShtName = "Окончательный"
'если существует "Окончательный" лист, будет добавлен еще один, например. "Финал_2"
За каждый шт в wb.Sheets
    Если Sht.Name = "Final" Тогда
        newShtName="Final"&"_"&wb.Sheets.Count 'как изменить эту часть?
    Конец, если
Следующий шт
НовоеШт.Имя = новоеШтИмя
  • Excel
  • ВБА

1

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

 'если существует "Финальный" лист, будет добавлен еще один, например. "Финал_2"
Dim cnt as Long
За каждый шт в wb.Sheets
    Если Left$(Sht. Name,5) = newShtName Then cnt = cnt + 1
Следующий шт
NewSht.Name = newShtName & IIF(cnt>0, "_" & cnt, "")

1

Этот код выполнит задание, он состоит из одного подпрограммы и одной функции

 Подпрограмма NewFinalSheet()
Dim wsNew As Worksheet
Если Не wsExits("Окончательный") Тогда
    Установите wsNew = thisworkbook.Worksheets.Add
    wsNew.Name = "Окончательный"
    Выйти из подпрограммы
Конец, если
Dim i As Long, newWsName As String
Делать
    я = я + 1
    newWsName = "Final_" & i
    Если Не wsExits(newWsName) Тогда
        Установите wsNew = ThisWorkbook.Sheets.Add
        wsNew.Name = новоеWsName
        Выйти из подпрограммы
    Конец, если
Петля
Конец сабвуфера

Функциональную часть можно выбрать одну из двух:
Это проще, но медленнее:

 Функция wsExits(shName As String) Как логическое значение
Dim ws As рабочий лист
Для каждого ws в этой книге. Рабочие листы
    Если ws.Name = shName Тогда
        wsExits = Истина
        Выход из функции
    Конец, если
Следующий мс
wsExits = Ложь
Конечная функция

Это должно быть быстрее, но фокус ошибки

 Функция wsExits(shName As String) As Boolean
 При ошибке Перейти к примечанию
 Если ThisWorkbook.