Создание, копирование, перемещение и удаление рабочих листов Excel с помощью кода VBA. Методы Sheets.Add, Worksheet.Copy, Worksheet.Move и Worksheet.Delete.
Создание новых листов
Создание новых рабочих листов осуществляется с помощью метода Sheets.Add.
Синтаксис метода Sheets.Add
expression.Add [Before, After, Count, Type]
где expression — переменная, представляющая собой объект Sheet.
Компоненты метода Sheets.Add
- Before* — необязательный параметр типа данных Variant, указывающий на лист, перед которым будет добавлен новый.
- After* — необязательный параметр типа данных Variant, указывающий на лист, после которого будет добавлен новый.
- Count — необязательный параметр типа данных Variant, указывающий, сколько листов будет добавлено (по умолчанию — 1).
- Type — необязательный параметр типа данных Variant, указывающий тип листа: xlWorksheet** (рабочий лист) или xlChart (диаграмма), по умолчанию — xlWorksheet.
*Если Before и After не указаны, новый лист, по умолчанию, будет добавлен перед активным листом.
**Для создания рабочего листа (xlWorksheet) можно использовать метод Worksheets.Add, который для создания диаграмм уже не подойдет.
Примеры создания листов
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
'Создание рабочего листа: Sheets.Add Worksheets.Add ThisWorkbook.Sheets.Add After:=ActiveSheet, Count:=2 Workbooks("Книга1.xlsm").Sheets.Add After:=Лист1 Workbooks("Книга1.xlsm").Sheets.Add After:=Worksheets(1) Workbooks("Книга1.xlsm").Sheets.Add After:=Worksheets("Лист1") 'Создание нового листа с заданным именем: Workbooks("Книга1.xlsm").Sheets.Add.Name = "Мой новый лист" 'Создание диаграммы: Sheets.Add Type:=xlChart 'Добавление нового листа перед 'последним листом рабочей книги Sheets.Add Before:=Sheets(Sheets.Count) 'Добавление нового листа в конец Sheets.Add After:=Sheets(Sheets.Count) |
- Лист1 в After:=Лист1 — это уникальное имя листа, указанное в проводнике редактора VBA без скобок.
- Лист1 в After:=Worksheets(«Лист1») — это имя на ярлыке листа, указанное в проводнике редактора VBA в скобках.
Создаваемый лист можно присвоить объектной переменной:
|
1 2 3 4 5 6 7 8 9 10 |
Dim myList As Object 'В активной книге Set myList = Worksheets.Add 'В книге «Книга1.xlsm» Set myList = Workbooks("Книга1.xlsm").Worksheets.Add 'Работаем с переменной myList.Name = "Listok1" myList.Cells(1, 1) = myList.Name 'Очищаем переменную Set myList = Nothing |
Если создаваемый лист присваивается объектной переменной, он будет помещен перед активным листом. Указать дополнительные параметры невозможно.
Копирование листов
Копирование рабочих листов осуществляется с помощью метода Worksheet.Copy.
Синтаксис метода Worksheet.Copy
expression.Copy [Before, After]
где expression — переменная, представляющая собой объект Worksheet.
Компоненты метода Worksheet.Copy
- Before* — необязательный параметр типа данных Variant, указывающий на лист, перед которым будет добавлена копия.
- After* — необязательный параметр типа данных Variant, указывающий на лист, после которого будет добавлена копия.
*Если Before и After не указаны, Excel создаст новую книгу и поместит копию листа в нее. Если скопированный лист содержит код в проекте VBA (в модуле листа), он тоже будет перенесен в новую книгу.
Примеры копирования листов
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
'В пределах активной книги '(уникальные имена листов) Лист1.Copy After:=Лист2 'В пределах активной книги '(имена листов на ярлычках) Worksheets("Лист1").Copy Before:=Worksheets("Лист2") 'Вставить копию в конец Лист1.Copy After:=Sheets(Sheets.Count) 'Из одной книги в другую Workbooks("Книга1.xlsm").Worksheets("Лист1").Copy _ After:=Workbooks("Книга2.xlsm").Worksheets("Лист1") 'Один лист активной книги в новую книгу Лист1.Copy 'Несколько листов активной книги в новую книгу* Sheets(Array("Лист1", "Лист2", "Лист3")).Copy 'Все листы книги с кодом в новую книгу ThisWorkbook.Worksheets.Copy |
* Если при копировании в новую книгу нескольких листов хотя бы один лист содержит умную таблицу — копирование невозможно. Один лист, содержащий умную таблицу, копируется в новую книгу без проблем.
Если рабочие книги указаны как элементы коллекции Workbooks, в том числе ActiveWorkbook и ThisWorkbook, листы нужно указывать как элементы коллекции Worksheets, использование уникальных имен вызовет ошибку.
Перемещение листов
Перемещение рабочих листов осуществляется с помощью метода Worksheet.Move.
Синтаксис метода Worksheet.Move
expression.Move [Before, After]
где expression — переменная, представляющая собой объект Worksheet.
Компоненты метода Worksheet.Move
- Before* — необязательный параметр типа данных Variant, указывающий на лист, перед которым будет размещен перемещаемый лист.
- After* — необязательный параметр типа данных Variant, указывающий на лист, после которого будет размещен перемещаемый лист.
*Если Before и After не указаны, Excel создаст новую книгу и переместит лист в нее.
Примеры перемещения листов
Простые примеры перемещения листов:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
'В пределах активной книги '(уникальные имена листов) Лист1.Move After:=Лист2 'В пределах активной книги '(имена листов на ярлычках) Worksheets("Лист1").Move Before:=Worksheets("Лист2") 'Размещение после последнего листа: Лист1.Move After:=Sheets(Sheets.Count) 'Из одной книги в другую Workbooks("Книга1.xlsm").Worksheets("Лист1").Move _ After:=Workbooks("Книга2.xlsm").Worksheets("Лист1") 'В новую книгу Лист1.Move |
Если рабочие книги указаны как элементы коллекции Workbooks, в том числе ActiveWorkbook и ThisWorkbook, листы нужно указывать как элементы коллекции Worksheets, использование уникальных имен вызовет ошибку.
Перемещение листа «Лист4» в позицию перед листом, указанным как по порядковому номеру, так и по имени ярлыка:
|
1 2 3 4 5 6 |
Sub Peremeshcheniye() Dim x x = InputBox("Введите имя или номер листа", "Перемещение листа «Лист4»") If IsNumeric(x) Then x = CLng(x) Sheets("Лист4").Move Before:=Sheets(x) End Sub |
Удаление листов
Удаление рабочих листов осуществляется с помощью метода Worksheet.Delete
Синтаксис метода Worksheet.Delete
expression.Delete
где expression — переменная, представляющая собой объект Worksheet.
Примеры удаления листов
|
1 2 3 4 5 6 7 8 9 10 11 |
'По уникальному имени Лист1.Delete 'По имени на ярлычке Worksheets("Лист1").Delete 'По индексу листа Worksheets(1).Delete 'В другой книге Workbooks("Книга1.xlsm").Worksheets("Лист1").Delete |
Если рабочие книги указаны как элементы коллекции Workbooks, в том числе ActiveWorkbook и ThisWorkbook, листы нужно указывать как элементы коллекции Worksheets, использование уникальных имен вызовет ошибку.
Как обратиться к рабочему листу, переименовать, скрыть или отобразить его с помощью кода VBA Excel, смотрите в этой статье.
Разобрался:
Здравствуйте, Евгений! У меня есть код, который сохраняет данные ячеек таблицы в текстовый файл. К сожалению он при этом меняет формат ячеек на текстовый для правильного переноса данных. Формулы в ячейках при этом дальше не работают, если, например, забудешь выходя из программы ничего не сохранять. У меня есть идея создать копию текущего листа в фоне, лучше чтобы он вообще был невидимым, сохранить данные уже с него, а потом закрыть этот лист не сохраняя его. Возможно ли что-то в этом роде?
Здравствуйте, Олег!
Могу предложить следующий вариант, но с видимым листом:
Добрый день. Подскажите, как должен выглядеть код для следующей задачи:
Необходимо два определенных листа из 10 (Реестр и Участники) с таблицами скопировать в новую книгу и сохранить под уникальным названием Дата_Реестр (2022-06-08_Реестр.xlsx)?
Добрый день, Александр!
Добрый день, помогите с кодом, нужен код, который при сборе данных одной книги с разных листов будет вставлять в результатирующую таблицу названия листов
нужен только код, который вставляет названия листов, спасибо
Здравствуйте, Мария!
Не знаю, какой код вам нужен, но, может быть, вам поможет пример вывода имен всех листов книги в первый столбец активного листа, за исключением имени самого активного листа:
Добрый день. подскажите пожалуйста, как правильно прописать макрос следующего характера:
Создается список с расписанием игр соревнований из этого списка необходимо сделать свой протокол под каждую игру, а также из предоставленных списков игроков добавить в протокол играющих команд
Здравствуйте, Евгений. Подскажите, пожалуйста, есть ли возможность отследить копирование листа в эту же книгу пользователем?
Здравствуйте, Михаил!
Код размещается в модуле книги.
Спасибо, Евгений. Этот фокус я знаю. Но надеялся, что именно копирование можно отследить. Придется работать со списком листов.
Option ExplicitSub MsgForEvg()
MsgBox Prompt:="Евгений, дай Бог вам здоровья." & vbCrLf _
& "Начитался комментов и понял что половина даже статью не прочла =(" & vbCrLf _
& "Многие статьи тут читал, но на комменты не обращал внимания." _
, Buttons:=vbOkOnly + vbInformation
End Sub
Обсуждение закрыто.