Vba excel создать новый лист: Как добавить новый лист и присвоить имя

Как добавить новый лист и присвоить имя

Автор Дмитрий Якушев На чтение 2 мин. Просмотров 1k.

Что делает макрос: Самый простой вариант автоматизации — добавить новый лист с присвоением ему конкретного имени.

Как макрос работает

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

Код макроса

Sub DobavitNoviiList()
'Шаг 1: Говорим Excel, что делать, если ошибка
On Error GoTo MyError
'Шаг 2: Добавляем лист
Sheets.Add
'Шаг 3: Присваиваем имя
ActiveSheet.Name = "Отчет"_
WorksheetFunction.Text(Now(),"yyyy")
'Шаг 4: Выходим
Exit Sub
'Шаг 5: Если произошла ошибка, сообщение пользователю
MyError:
MsgBox "Лист с таким именем уже есть!"
End Sub

Как работает макрос

1. Вы знаете, что если присвоить новому листу имя, которое уже есть, то возникнет ошибка. Таким образом, на шаге 1, макрос говорит Excel немедленно перейти к строке, которая говорит MyError (на шаге 3), если есть ошибка.
2. Для создания листа используем метод Add. По умолчанию, лист называется SheetХХ, где хх представляет число листа. Мы даем листу новое имя путем изменения свойства объекта ActiveSheet.Name в этом случае мы именуем рабочий лист «Отчёт и текущий год».
3. Как и в рабочих книгах, каждый раз, когда вы добавляете новый лист с помощью VBA, он автоматически становится активным. Именно поэтому мы пишем ActiveSheet.Name.
4. Обратите внимание, что на шаге 4 мы выходим из процедуры. Делаем так, чтобы он случайно не показал сообщение об ошибке.
5. Данный шаг запускается, если имя нового листа совпадает с уже существующим в книге. С помощью сообщения уведомляем пользователя об этом. Опять же, этот шаг должен быть выполнен только в случае возникновения ошибки.

Как использовать

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

1. Активируйте редактор Visual Basic, нажав ALT + F11.
2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
3. Выберите Insert➜Module.
4. Введите или вставьте код во вновь созданном модуле.

VBA Excel. Рабочий лист (обращение, переименование, скрытие)

Обращение к рабочим листам Excel из кода VBA. Переименование листов, скрытие и отображение с помощью кода VBA Excel. Свойства Worksheets.Name и Worksheets.Visible.

Обращение к рабочим листам

Рабочий лист (Worksheet) принадлежит коллекции всех рабочих листов (Worksheets) книги Excel. Обратиться к листу можно как к элементу коллекции и, напрямую, по его уникальному имени.

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

Обращение к рабочему листу в коде VBA Excel:

• УникИмяЛиста – уникальное имя листа, отображаемое в проводнике редактора VBA без скобок, с помощью кода VBA изменить его невозможно.
• N – индекс листа от 1 до количества всех листов в книге, соответствует порядковому номеру ярлычка этого листа в открытой книге Excel.
• Имя листа – имя листа, отображаемое в проводнике редактора VBA в скобках, с помощью кода VBA изменить его можно.

Количество листов в рабочей книге Excel определяется так:

Переименование листов

В VBA Excel есть некоторые особенности в наименовании листов, так как у рабочего листа есть два свойства, связанных с именем: (Name) и Name. Откройте окно «Properties» в редакторе VBA, нажав клавишу «F4», и выделите любой лист в проводнике. Вы увидите, что в окне «Properties» свойству (Name) в скобках соответствует в проводнике уникальное имя листа без скобок, а свойству Name без скобок соответствует изменяемое имя листа в скобках. Оба имени в окне «Properties» можно редактировать.

С помощью кода VBA Excel можно редактировать только имя листа Name, отображаемое на ярлычке листа и в проводнике без скобок. Для этого используется свойство рабочего листа Worksheets.Name со следующим синтаксисом:

expression.Name

где expression – переменная, представляющая собой объект Worksheet. Смена имени осуществляется путем присвоения нового значения свойству Worksheets.Name.

Допустим, у нас есть лист с уникальным именем (Name) – Лист1, индексом – 1 и именем Name – МойЛист, которое необходимо заменить на имя – Реестр.

Скрытие и отображение листов

Для скрытия и отображения рабочих листов в VBA Excel используется свойство Worksheet.Visible со следующим синтаксисом:

expression.Visible

где expression – переменная, представляющая собой объект Worksheet. Свойству Worksheet.Visible могут присваиваться следующие значения:

• False – лист становится невидимым, но он будет присутствовать в списке скрытых листов, и пользователь сможет его отобразить с помощью инструментов рабочей книги Excel.
• xlVeryHidden – лист становится супер невидимым и его не будет в списке скрытых листов, пользователь не сможет его отобразить. Актуально для Excel 2003-2016.
• True – лист становится видимым.

Аналоги присваиваемых значений:

• False = xlHidden = xlSheetHidden = 1
• xlVeryHidden = xlSheetVeryHidden = 2
• True = xlSheetVisible = -1 (константа xlVisible вызывает ошибку)

Примеры:

Как создать, скопировать, переместить или удалить рабочий лист с помощью кода VBA Excel, смотрите в этой статье.

VBA Excel. Создание файлов

Создание файлов Excel методами Workbooks.Add, Worksheet.Copy и текстовых файлов с помощью оператора Open и метода CreateTextFile из кода VBA Excel. Создание документов Word рассмотрено в отдельной статье.

Создание файлов Excel

Метод Workbooks.Add

Описание

Файлы Excel можно создавать из кода VBA с помощью метода Add объекта Workbooks.

Workbooks.Add – это метод, который создает и возвращает новую книгу Excel. Новая книга после создания становится активной.

Ссылку на новую книгу Excel, созданную методом Workbooks.Add, можно присвоить объектной переменной с помощью оператора Set или обращаться к ней, как к активной книге: ActiveWorkbook.

Синтаксис

Workbooks.Add (Template)

Template – параметр, который определяет, как создается новая книга.

Примеры

Пример 1
Создание новой книги Excel с количеством листов по умолчанию и сохранение ее в папку, где расположен файл с кодом VBA:

Файл «Моя новая книга.xlsx» понадобится для следующего примера.

Пример 2
Создание новой книги по файлу «Моя новая книга.xlsx» в качестве шаблона с присвоением ссылки на нее объектной переменной, сохранение нового файла с новым именем и добавление в него нового рабочего листа:

Метод Worksheet.Copy

Описание

Если в коде VBA Excel применить метод Worksheet.Copy без указания параметра Before или After, будет создана новая книга с копируемым листом (листами). Новая книга станет активной.

Примеры

Пример 3
Создание новой книги с помощью копирования одного листа (в этом примере используется книга, созданная в первом примере):

Также, как и при создании нового файла Excel методом Workbooks.Add, при создании новой книги методом Worksheet.Copy, можно ссылку на нее присвоить объектной переменной.

Пример 4
Создание новой книги, в которую включены копии всех рабочих листов из файла с кодом VBA:

Пример 5
Создание новой книги, в которую включены копии выбранных рабочих листов из файла с кодом VBA:

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

Оператор Open

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

Пример

В имени текстового файла пробелы заменены дефисами (знаками минус), так как метод Run объекта Wscript.Shell не способен открывать файлы с именами, содержащими пробелы.

Метод FileSystemObject.CreateTextFile

Для создания нового текстового файла из кода VBA Excel по указанному имени, можно использовать метод CreateTextFile объекта FileSystemObject.

Пример

Стоит отметить, что новый текстовый файл может быть создан и с помощью метода OpenTextFile объекта FileSystemObject при условии присвоения параметру create значения True.

Создать листы из списка | Excel для всех

MulTEx » 20 Март 2018       Дмитрий       2499 просмотров

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

Вызов команды:
MulTEx -группа Ячейки/Диапазоны —Диапазоны —Создать листы из списка

С помощью данной команды можно быстро создать множество листов, сразу назначив им имена либо на основании диапазона ячеек, либо нумерацией(с указанием начальной и конечной границ). Так же можно указать, создавать листы как копии другого листа(шаблона) или после создания листов копировать в них указанную таблицу.
Для чего это может быть нужно? Например, для создания листов по определенному шаблону на каждый день месяца(от 1 до 31).

Создавая листы, использовать имена:

• из ячеек диапазона — указывается несколько ячеек, в которых записаны имена для создаваемых листов. Например, если необходимо создать листы с именами месяцев, можно в ячейки A1:A12 записать названия месяцев:

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

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

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

В качестве наполнения создаваемых листов использовать — здесь можно указать создавать пустые листы или в них должно быть какое-то содержимое(на основании некоего шаблона).

• Лист целиком — в этом случае каждый созданный лист будет в точности повторять выбранный лист(форматирование, формулы, имена, значения). Различаться будут только имена. В данном случае лист-шаблон должен обязательно располагаться в активной на момент выполнения команды книге.
• Диапазон ячеек — в данном случае на каждый созданный лист будут вставлены ячейки указанного диапазона.
  вставлять данные, начиная с ячейки — указывается ячейка, с которой начинать вставку Диапазона ячеек. Если не указана — будет использована ячейка A1.
• Создавать пустые листы — будут созданы пустые листы.

Создавать листы

• в текущей книге — листы будут добавлены после последнего листа активной книги.
• в новой книге — будет создана новая книга, в которой б

Макрос на VBA Excel – Формируем документы по шаблону | Info-Comp.ru

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

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

Мы с Вами уже выгружали данные по шаблону через клиент Access из базы MSSql 2008 в Word и Excel вот в этой статье —  Выгрузка данных из Access в шаблон Word и Excel. Но сейчас допустим, у нас данные располагаются в базе, в клиенте которой нельзя или слишком трудоемко реализовать такую задачу, поэтому мы просто выгрузим необходимые данные в Excel и на основе таких данных по шаблону сформируем наши документы.

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

Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля

И так приступим!

Пишем макрос на VBA Excel по формированию документов

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

Примечание! Программировать будем в Excel 2010.

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

Данные.

Лист, на котором расположены эти данные так и назовем «Данные»

Шаблон.

Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»

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

Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»

Свои поля я назвал следующим образом:

• ФИО – fio;
• № — number;
• Должность – dolgn;
• Адрес проживания – addres;
• Тел. № сотрудника – phone;
• Комментарий – comment.

Код макроса на VBA Excel

Для того чтобы написать код макроса, открывайте на ленте вкладку «Разработчик», далее макросы.

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

Далее, когда Вы откроете вкладку разработчик и нажмете кнопку «Макросы» у Вас отобразится окно создания макроса, Вы пишите название макросы и жмете «создать».

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

Sub Карточка()
'Книга
NewBook = ""
' Путь, где будут храниться наши карточки
' Т.е. в той папке, откуда запустился файл с макросом
Path = ThisWorkbook.Path
' Выбираем лист с данными
Sheets("Данные").Select
' Запускаем цикл, скажем на 100000 итераций
' Начиная со второй строки, не учитывая заголовок
For i = 2 To 100000
' Выйдем из него, когда фамилии закончатся, т.е. строки
If Cells(i, 1).Value = "" Then
        i = 100000
    Exit For
End If
' Имя файла карточки, назовем по фамилии
Name_file = Path & "\" & Sheets("Данные").Cells(i, 1).Value & ".xls"
‘Выбираем лист с шаблоном
Sheets("Шаблон").Select
' Присваиваем значения нашим ячейкам, по именам которые мы задавали
    Range("fio").Value = Sheets("Данные").Cells(i, 1).Value & " " & _
        Sheets("Данные").Cells(i, 2).Value & " " & Sheets("Данные").Cells(i, 3).Value
    Range("number").Value = Sheets("Данные").Cells(i, 4).Value
    Range("addres").Value = Sheets("Данные").Cells(i, 5).Value
    Range("dolgn").Value = Sheets("Данные").Cells(i, 6).Value
    Range("phone").Value = Sheets("Данные").Cells(i, 7).Value
    Range("comment").Value = Sheets("Данные").Cells(i, 8).Value
    ' Копируем все
    Cells.Select
    Selection.Copy
    ' Создаем новую книгу или делаем ее активной 
    If NewBook = "" Then
        Workbooks.Add
        NewBook = ActiveWorkbook.Name
    Else
        Workbooks(NewBook).Activate
        Cells(1, 1).Select
    End If
    ' Вставляем данные в эту книгу
    Application.DisplayAlerts = False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ' Сохраняем с нашим новым названием
    ActiveWorkbook.SaveAs Filename:= _
    Name_file, FileFormat:=xlExcel8, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    NewBook = ActiveWorkbook.Name
    Application.DisplayAlerts = True
    ' Снова активируем файл с макросом и выбираем лист
    Workbooks("Макрос.xls").Activate
    Sheets("Данные").Select
' Переходим к следующей строке
Next i
' Закроем книгу
Workbooks(NewBook).Close
' Выведем сообщение об окончании
MsgBox ("Выполнено!")
End Sub

Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

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

Нравится5Не нравится

VBA Excel. Генерация документов и отчетов

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

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

Создание документа или отчета из шаблона

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

Шаблон создаем вручную на отдельном рабочем листе или с помощью кода VBA при заполнении документа. Способы создания шаблона определяют место его хранения:

• В коде VBA. Такой способ возможен для несложных печатных форм.
• В рабочей книге Excel с реестром или базой данных на скрытом листе.
• В отдельном файле, который не обязательно сохранять как шаблон Excel (*.xlt*).

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

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

Назовем лист с шаблоном документа или отчета – «Shablon». Для создания новой печатной формы скрытый лист с шаблоном необходимо скопировать:

Данный код вставит новый лист с шаблоном в ту же книгу после всех имеющихся листов. Чтобы шаблон скопировать в новую книгу, заменяем строку .Copy After:=Worksheets(Worksheets.Count) на .Copy. Печатная форма откроется в новом файле.

Сразу после создания, обращаться к новому листу можно как к активному (ActiveSheet), даже в том случае, если он скопирован в новую книгу. Чтобы воспользоваться этой возможностью, порядок действий в программе должен быть следующим:

1. Копирование информации в переменную массива из реестра или базы данных.
2. Создание нового документа или отчета из шаблона.
3. Заполнение документа или отчета, обращаясь к нему как к ActiveSheet.

Копирование информации и заполнение формы

Копирование информации

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

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

Допустим, что лист называется «Reestr», а необходимая информация расположена в десяти первых ячейках каждой строки:

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

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

Если требуется обработка данных для отчета, проводим ее в массиве. Для результатов обработки можно объявить еще одну переменную массива.

Создание и заполнение формы

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

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

Чтобы не наблюдать за мерцаниями экрана во время выполнения процедуры VBA Excel, можно временно отключить его обновления:

Удаление печатной формы

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

Для этого необходимо на лист шаблона правее печатной формы вставить кнопку из коллекции «Элементы управления ActiveX». Дважды кликнуть по ней и в открывшемся модуле листа внутрь автоматически сгенерированного объявления процедуры вставить строку Call UdaleniyeLista:

Как создать файл Excel с помощью VBA

Автор Дмитрий Якушев На чтение 3 мин. Просмотров 3.3k.

Что делает макрос: Следующий макрос копирует диапазон ячеек из активного листа и вставляет данные в новую книгу.

Код макроса

Sub SozdatFajl()
'Шаг 1 - копируем данные с листа
Sheets("Продажи").Range("B4:C15").Copy

'Шаг 2 - создаем новую книгу
Workbooks.Add

'Шаг 3 - вставляем данные
ActiveSheet.Paste Destination:=Range("A1")

'Шаг 4 - отключаем системные сообщения
Application.DisplayAlerts = False

'Шаг 5 - сохраняем по нужному адресу
ActiveWorkbook.SaveAs _
Filename:="C:\Отчеты\ПродажиМесяц.xlsx"

'Шаг 6 - включаем системные сообщения
Application.DisplayAlerts = True
End Sub

Как работает этот код

1. Копируем данные из ячеек B4:С15. Обратите внимание, что мы указываем полный адрес с именем листа и диапазона. Это позволит не допустить ошибки, если у Вас открыто несколько файлов Excel одновременно.
2. Используем метод Add объекта Workbook, чтобы создать новую рабочую книгу. Это тоже самое, если бы мы вручную нажали Файл➜Создать➜Новый документ
3. На этом этапе используется метод Paste, чтобы отправить данные, которые вы скопировали в ячейку А1 новой книги. Обратите внимание на то, что код ссылается на объект  ActiveSheet. При создании новой книги она тут же становится активной. Если быть точнее, то становится активный лист в новой (только что созданной) книге.
4. Далее используем метод DisplayAlerts. Используя свойство False — отключаем системные предупреждения Excel. Можно этого не делать, но мы можем запустить этот макрос несколько раз, и в этом случае Excel будет пытаться сохранить один и тот же файл несколько раз. Что происходит, когда вы пытаетесь сохранить книгу несколько раз? Правильно — Excel предупреждает, что в папке уже есть файл с таким же именем и спрашивает: «Хотите ли вы переписать ранее существующий файл?». Если мы не хотим, чтобы Excel при каждом сохранении спрашивал нас — подавляем это предупреждение.
5. Сохраняем файл с помощью метода SaveAs. Обратите внимание, что мы пишем полный адрес, включая имя файла с расширением (при сохранении на рабочий стол
   ActiveWorkbook.SaveAs CreateObject(«WScript.Shell»). SpecialFolders(«Desktop») & «\Отчет на 2016.xlsx»).
6. Поскольку мы запретили показывать системные сообщения в пункте 4, мы должны включить их обратно. Если этого не сделать Excel будет подавлять все предупреждения, что не
   допустимо.

Как использовать

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

1. Активируйте редактор Visual Basic, нажав ALT + F11.
2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
3. Выберите Insert➜Module.
4. Введите или вставьте код во вновь созданном модуле.
5. Измените имя листа, диапазон ячеек, и место сохранения файла.

Код VBA

для создания нового листа из шаблона и переименования его из списка

  Опция Явная

Sub SheetsFromTemplate ()
Джерри Бокер - 22 октября 2014 г.
'Создание копий шаблона листа, используя текст на мастер-листе в определенном столбце
'Строки имени листа исправлены с помощью UDF ниже
Dim wsMASTER как рабочий лист, wsTEMP как рабочий лист, wasVISIBLE как логическое
Dim shNAMES как диапазон, Nm как диапазон, NmSTR как строка

С помощью ThisWorkbook 'сосредоточьтесь на этой книге
    Установите wsTEMP =.Листы ("Шаблон") 'лист для копирования
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible) 'проверьте, скрыт он или нет
    Если Not wasVISIBLE, то wsTEMP.Visible = xlSheetVisible 'сделать его видимым
    
    Установить лист wsMASTER = .Sheets ("Master") с именами
                                                                'диапазон для поиска имен для проверки
    Установите shNAMES = wsMASTER.Range ("B3: B" & Rows.Count) .SpecialCells (xlConstants) 'или xlFormulas
    
    Заявка.ScreenUpdating = False 'макрос ускорения
    Для каждого Nm In shNAMES проверяйте одно имя за раз
        NmSTR = FixStringForSheetName (CStr (Nm.Text)) 'использовать UDF для создания допустимого имени листа
        If Not Evaluate ("ISREF ('" & NmSTR & "'! A1)") Then ', если лист не существует ...
            wsTEMP.Copy After: =. Sheets (.Sheets.Count) '... создать его из шаблона
            ActiveSheet.Name = NmSTR '...назовите это
        Конец, если
    Следующий Нм
    
    wsMASTER.Activate 'возврат к мастер-листу
    Если Not wasVISIBLE, то wsTEMP.Visible = xlSheetHidden 'при необходимости скрыть шаблон
    Application.ScreenUpdating = True 'обновить экран один раз в конце
Конец с

MsgBox «Все листы созданы»
Конец подписки


Функция FixStringForSheetName (shSTR как строка) как строка

'заменить каждый запрещенный символ чем-нибудь приемлемым
    shSTR = Заменить (shSTR, ":", "")
    shSTR = Заменить (shSTR, "?", "")
    shSTR = Заменить (shSTR, "*", "")
    shSTR = Заменить (shSTR, "/", "-")
    shSTR = Заменить (shSTR, "\", "-")
    shSTR = Заменить (shSTR, "[", "(")
    shSTR = Заменить (shSTR, "]", ")")

'имена листов могут состоять только из 31 символа
    FixStringForSheetName = Обрезать (слева (shSTR, 31))

Конечная функция  

.

Работа с таблицами с использованием Excel VBA (объяснение с примерами)

Помимо ячеек и диапазонов, работа с листами — еще одна область, о которой вы должны знать, чтобы эффективно использовать VBA в Excel.

Как и любой объект в VBA, рабочие листы имеют различные свойства и методы, связанные с ними, которые можно использовать при автоматизации работы с VBA в Excel.

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

Итак, приступим.

Все коды, которые я упоминаю в этом руководстве, необходимо поместить в редактор VB. Перейдите в раздел «Где разместить код VBA», чтобы узнать, как это работает.

Если вы хотите изучить VBA простым способом, ознакомьтесь с моим курсом Online Excel VBA Training .

Разница между листами и листами в VBA

В VBA у вас есть две коллекции, которые иногда могут немного сбивать с толку.

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

В Excel VBA:

• Коллекция «Рабочие листы» будет относиться к коллекции всех объектов рабочего листа в книге. В приведенном выше примере коллекция Worksheets будет состоять из трех рабочих листов.
• Коллекция «Таблицы» будет относиться ко всем рабочим таблицам, а также к таблицам диаграмм в книге. В приведенном выше примере у него будет четыре элемента — 3 листа + 1 лист диаграммы.

Если у вас есть рабочая книга, в которой есть только рабочие листы и нет листов диаграмм, тогда коллекции «Рабочие листы» и «Таблицы» будут одинаковыми.

Но когда у вас есть один или несколько листов диаграмм, коллекция «Таблицы» будет больше, чем коллекция «Таблицы»

листов = Рабочие листы + Таблицы

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

Итак, если вам нужно ссылаться только на листы, используйте коллекцию «Рабочие листы», а если вам нужно ссылаться на все листы (включая листы диаграмм), используйте коллекцию «Листы».

В этом уроке я буду использовать только коллекцию «Рабочие листы».

Ссылка на лист в VBA

Существует много разных способов обращения к рабочему листу в VBA.

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

Использование имени рабочего листа

Самый простой способ сослаться на рабочий лист — использовать его имя.

Например, предположим, что у вас есть рабочая книга с тремя листами — Лист 1, Лист 2, Лист 3.

И вы хотите активировать Лист 2.

 Вы можете сделать это с помощью следующего кода:

Sub ActivateSheet ()
Рабочие листы ("Лист2"). Активировать
Концевой переводник 

Приведенный выше код просит VBA обратиться к Sheet2 в коллекции Worksheets и активировать его.

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

 Sub ActivateSheet ()
Таблицы ("Лист2"). Активировать
Концевой переводник 

Использование порядкового номера

Хотя использование имени листа — это простой способ сослаться на лист, иногда вы можете не знать точное имя листа.

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

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

Предположим, у вас в книге есть следующие листы:

Приведенный ниже код активирует Sheet2:

 Sub ActivateSheet ()
Рабочие листы (2) .Активировать
Концевой переводник 

Обратите внимание, что мы использовали порядковый номер 2 в Рабочих листах (2) .Это будет относиться ко второму объекту в наборе листов.

Теперь, что происходит, когда вы используете 3 в качестве порядкового номера?

Будет выбран Sheet3.

Если вам интересно, почему он выбрал Sheet3, поскольку это явно четвертый объект.

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

Таким образом, когда мы используем номера индексов в коллекции Worksheets, они будут ссылаться только на рабочие листы в книге (и игнорировать листы диаграмм).

Напротив, если вы используете Sheets, Sheets (1) будет ссылаться на Sheets1, Sheets (2) будет ссылаться на Sheet2, Sheets (3) будет ссылаться на Chart1, а Sheets (4) будет ссылаться на Sheet3.

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

Примечание: порядковый номер идет слева направо.Итак, если вы переместите Sheet2 влево от Sheet1, тогда Worksheets (1) будет ссылаться на Sheet2.

Использование кодового имени рабочего листа

Одним из недостатков использования имени листа (как мы видели в разделе выше) является то, что пользователь может его изменить.

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

Чтобы решить эту проблему, вы можете использовать кодовое имя рабочего листа (вместо обычного имени, которое мы использовали до сих пор).Кодовое имя может быть назначено в редакторе VB Editor, и оно не меняется при изменении имени листа в области рабочего листа.

Чтобы присвоить листу кодовое имя, выполните следующие действия:

1. Щелкните вкладку Разработчик.
2. Щелкните кнопку Visual Basic. Это откроет редактор VB.
3. Выберите в меню пункт «Просмотр» и щелкните «Окно проекта». Это сделает видимой панель свойств. Если панель «Свойства» уже отображается, пропустите этот шаг.
4. Щелкните имя листа в проводнике проекта, который вы хотите переименовать.
5. На панели «Свойства» измените имя в поле перед (Имя). Учтите, что в названии не должно быть пробелов.

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

На приведенном выше изображении имя листа — «SheetName», а кодовое имя — «CodeName».Даже если вы измените имя листа на листе, кодовое имя останется прежним.

Теперь вы можете использовать коллекцию Worksheets для ссылки на рабочий лист или использовать кодовое имя.

Например, обе строки активируют рабочий лист.

 рабочих листов («Имя листа»). Активировать
CodeName.Activate 

Разница между этими двумя таблицами состоит в том, что если вы измените имя рабочего листа, первое не будет работать. Но вторая строка продолжала бы работать даже с измененным именем.Вторая строка (с использованием CodeName) также короче и проще в использовании.

Обращение к листу в другой книге

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

Например, если у вас есть книга с именем Примеры и вы хотите активировать Лист1 в книге Пример, вам необходимо использовать следующий код:

 Дополнительный лист Активировать ()
Рабочие тетради ("Примеры.xlsx "). Worksheets (" Sheet1 "). Активировать
Концевой переводник 

Обратите внимание, что если рабочая книга была сохранена, вам необходимо использовать имя файла вместе с расширением. Если вы не знаете, какое имя использовать, обратитесь за помощью в Project Explorer.

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

Добавление рабочего листа

Приведенный ниже код добавит рабочий лист (как первый рабочий лист, то есть как крайний левый лист на вкладке листа).

 Дополнительная таблица ()
Рабочие листы.Добавить
Концевой переводник 

Требуется имя по умолчанию Sheet2 (или любое другое число в зависимости от того, сколько листов уже есть).

Если вы хотите добавить рабочий лист перед определенным листом (скажем, Sheet2), вы можете использовать приведенный ниже код.

 Дополнительная таблица ()
Worksheets.Add Before: = Worksheets ("Sheet2")
Концевой переводник 

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

Точно так же вы также можете добавить лист после рабочего листа (скажем, Sheet2), используя следующий код:

 Дополнительная таблица ()
Рабочие листы.Добавить после: = Рабочие листы ("Лист2")
Концевой переводник 

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

 Дополнительная таблица ()
Dim SheetCount как целое число
SheetCount = Рабочие листы.Считать
Рабочие листы.Добавить после: = Рабочие листы (SheetCount)
Концевой переводник 

Удаление рабочего листа

Приведенный ниже код удалит активный лист из книги.

 Sub DeleteSheet ()
ActiveSheet.Delete
Концевой переводник 

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

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

 Sub DeleteSheet ()
Application.DisplayAlerts = False
ActiveSheet.удалять
ActiveWindow.SelectedSheets.Delete
Концевой переводник 

Если для Application.DisplayAlerts задано значение False, предупреждение не будет отображаться. Если вы его используете, не забудьте вернуть его в True в конце кода.

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

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

 Sub DeleteSheet ()
Рабочие листы ("Лист2"). Удалить
Концевой переводник 

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

 Sub DeleteSheet ()
Лист5. Удалить
Концевой переводник 

Переименование рабочих листов

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

Следующий код изменит имя Sheet1 на «Summary».

 Дополнительная таблица переименования ()
Рабочие листы ("Sheet1"). Name = "Summary"
Концевой переводник 

Вы можете объединить это с методом добавления листов, чтобы получить набор листов с определенными именами.

Например, если вы хотите вставить четыре листа с именами 2018 Q1, 2018 Q2, 2018 Q3 и 2018 Q4, вы можете использовать приведенный ниже код.

 Дополнительная таблица переименования ()
Тусклые таблицы как целые числа
Countsheets = Worksheets.Count
Для i = от 1 до 4
Рабочие листы.Добавить после: = Рабочие листы (Таблицы + i - 1)
Рабочие листы (Таблицы + i) .Name = "2018 Q" & i
Далее я
Концевой переводник 

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

Назначение объекта рабочего листа переменной

При работе с листами вы можете назначить рабочий лист переменной объекта, а затем использовать эту переменную вместо ссылок на лист.

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

Вот код, который добавит 2018 в качестве префикса ко всем именам рабочего листа.

 Дополнительная таблица переименования ()
Dim Ws как рабочий лист
Для каждой буквы W в листах
Ws.Name = "2018 -" & Ws.Name
Следующий Ws
Концевой переводник 

В приведенном выше коде в качестве типа рабочего листа объявляется переменная Ws (с использованием строки «Dim Ws As Worksheet»).

Теперь нам не нужно подсчитывать количество листов, чтобы их просмотреть. Вместо этого мы можем использовать цикл «Для каждой буквы W в листах». Это позволит нам просмотреть все листы в коллекции листов. Неважно, 2 листа или 20 листов.

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

В приведенном ниже коде мы назначаем переменную Ws для Sheet2 и используем ее для доступа ко всем свойствам Sheet2.

 Дополнительная таблица переименования ()
Dim Ws как рабочий лист
Установить Ws = Worksheets ("Sheet2")
Ws.Name = "Сводка"
Ws.Protect
Концевой переводник 

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

Обратите внимание, что код объявляет объект Ws как переменную типа Worksheet (используя строку Dim Ws как Worksheet).

Скрыть рабочие листы с помощью VBA (скрытый + очень скрытый)

Скрытие и отображение листов в Excel — простая задача.

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

Но что, если вы не хотите, чтобы они могли отображать лист (ы).

Это можно сделать с помощью VBA.

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

 Подложка HideAllExcetActiveSheet ()
Dim Ws как рабочий лист
Для каждой W в ThisWorkbook.Worksheets
Если Ws.Name <> ActiveSheet.Name, то Ws.Visible = xlSheetVeryHidden
Следующий Ws
Концевой переводник 

В приведенном выше коде свойство Ws.Visible изменено на xlSheetVeryHidden .

• Если для свойства Visible задано значение xlSheetVisible, лист отображается в области рабочего листа (как вкладки рабочего листа).
• Если для свойства Visible задано значение xlSheetHidden, лист скрыт, но пользователь может отобразить его, щелкнув правой кнопкой мыши на любой вкладке листа.
• Если для свойства Visible задано значение xlSheetVeryHidden, лист скрыт и не может быть отображен из области рабочего листа. Вам нужно использовать код VBA или окно свойств, чтобы отобразить его.

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

 Sub HideAllExceptActiveSheet ()
Dim Ws как рабочий лист
Для каждой W в ThisWorkbook.Worksheets
Если Ws.Name <> ActiveSheet.Name, то Ws.Visible = xlSheetHidden
Следующий Ws
Концевой переводник 

Приведенный ниже код покажет все рабочие листы (как скрытые, так и очень скрытые).

 Sub UnhideAllWoksheets ()
Dim Ws как рабочий лист
Для каждой W в ThisWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Следующий Ws
Концевой переводник 

  Связанная статья : Показать все листы в Excel (за один раз) 

Скрыть листы на основе текста в нем

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

Это можно сделать с помощью функции VBA INSTR.

Приведенный ниже код скроет все листы, кроме тех, на которых есть текст 2018.

 Подложка HideWithMatchingText ()
Dim Ws как рабочий лист
Для каждой буквы W в листах
Если InStr (1, Ws.Name, «2018», vbBinaryCompare) = 0, то
Ws.Visible = xlSheetHidden
Конец, если
Следующий Ws
Концевой переводник 

В приведенном выше коде функция INSTR возвращает позицию символа, в которой она находит соответствующую строку. Если подходящей строки не удается найти, возвращается 0.

Приведенный выше код проверяет, есть ли в имени текст 2018. Если это так, ничего не происходит, иначе рабочий лист скрыт.

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

Сортировка листов в алфавитном порядке

Используя VBA, вы можете быстро отсортировать листы по их именам.

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

 Sub SortSheetsTabName ()
Application.ScreenUpdating = False
Dim ShCount как целое число, i как целое, j как целое
ShCount = Sheets.Count
Для i = 1 To ShCount - 1
Для j = i + 1 To ShCount
Если Sheets (j) .Name 
 Обратите внимание, что этот код хорошо работает с текстовыми именами, а также в большинстве случаев с годами и числами. Но это может дать неправильные результаты, если у вас есть имена листов 1,2,11. Он отсортирует и выдаст вам последовательность 1, 11, 2. Это потому, что он выполняет сравнение как текст и считает, что 2 больше 11.
 Защита / снятие защиты со всех листов за один раз 
 Если у вас много листов в книге и вы хотите защитить все листы, вы можете использовать приведенный ниже код VBA.
 Позволяет указать пароль внутри кода. Этот пароль понадобится вам, чтобы снять защиту с листа.
 Sub ProtectAllSheets ()
Dim ws как рабочий лист
Тусклый пароль как строка
password = "Test123" 'замените Test123 желаемым паролем
Для каждого ws в листах
ws.Protect пароль: = пароль
Следующий ws
Концевой переводник 
 Следующий код снимет защиту со всех листов за один раз.
 Sub ProtectAllSheets ()
Dim ws как рабочий лист
Тусклый пароль как строка
password = "Test123" 'замените Test123 на пароль, который вы использовали при защите
Для каждого ws в листах
ws.Снять защиту пароль: = пароль
Следующий ws
Концевой переводник 
 Создание оглавления всех рабочих листов (с гиперссылками) 
 Если у вас есть набор рабочих листов в книге и вы хотите быстро вставить сводный лист со ссылками на все листы, вы можете использовать приведенный ниже код.
 Sub AddIndexSheet ()
Рабочие листы.Добавить
ActiveSheet.Name = "Индекс"
Для i = 2 To Worksheets.Count
ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _
Адрес: = "", Дополнительный адрес: = Рабочие листы (i).Имя & "! A1", _
TextToDisplay: = Рабочие листы (i). Имя
Далее я
Концевой переводник 
 Приведенный выше код вставляет новый рабочий лист и называет его Index.
 Затем он просматривает все рабочие листы и создает гиперссылки для всех рабочих листов на индексном листе.
 Где разместить код VBA 
 Хотите знать, где находится код VBA в вашей книге Excel?
 Excel имеет серверную часть VBA, называемую редактором VBA. Вам необходимо скопировать и вставить код в окно кода модуля VB Editor.
 Вот шаги, чтобы сделать это:
 Перейдите на вкладку Разработчик.
 Щелкните параметр Visual Basic. Это откроет редактор VB в бэкэнде.
 В панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
 Перейдите во вкладку "Вставить" и щелкните "Модуль". Это вставит объект модуля для вашей книги.
 Скопируйте и вставьте код в окно модуля.
  Вам также могут понравиться следующие руководства по Excel VBA: 
 .
 18 готовых макросов VBA Excel 
 Переключить навигацию
 Главная
 Блог

 ru:
 .
No related posts.