VBA Excel. Рабочий лист (создание, копирование, удаление)

Создание, копирование, перемещение и удаление рабочих листов 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 в After:=Лист1 — это уникальное имя листа, указанное в проводнике редактора VBA без скобок.
  • Лист1 в After:=Worksheets(«Лист1») — это имя на ярлыке листа, указанное в проводнике редактора VBA в скобках.

Создаваемый лист можно присвоить объектной переменной:

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

Копирование листов

Копирование рабочих листов осуществляется с помощью метода Worksheet.Copy.

Синтаксис метода Worksheet.Copy

expression.Copy [Before, After]

где expression — переменная, представляющая собой объект Worksheet.

Компоненты метода Worksheet.Copy

  • Before* — необязательный параметр типа данных Variant, указывающий на лист, перед которым будет добавлена копия.
  • After* — необязательный параметр типа данных Variant, указывающий на лист, после которого будет добавлена копия.

*Если Before и After не указаны, Excel создаст новую книгу и поместит копию листа в нее. Если скопированный лист содержит код в проекте VBA (в модуле листа), он тоже будет перенесен в новую книгу.

Примеры копирования листов

Если рабочие книги указаны как элементы коллекции Workbooks, в том числе ActiveWorkbook и ThisWorkbook, листы нужно указывать как элементы коллекции Worksheets, использование уникальных имен вызовет ошибку.

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

Перемещение рабочих листов осуществляется с помощью метода Worksheet.Move.

Синтаксис метода Worksheet.Move

expression.Move [Before, After]

где expression — переменная, представляющая собой объект Worksheet.

Компоненты метода Worksheet.Move

  • Before* — необязательный параметр типа данных Variant, указывающий на лист, перед которым будет размещен перемещаемый лист.
  • After* — необязательный параметр типа данных Variant, указывающий на лист, после которого будет размещен перемещаемый лист.

*Если Before и After не указаны, Excel создаст новую книгу и переместит лист в нее.

Примеры перемещения листов

Простые примеры перемещения листов:

Если рабочие книги указаны как элементы коллекции Workbooks, в том числе ActiveWorkbook и ThisWorkbook, листы нужно указывать как элементы коллекции Worksheets, использование уникальных имен вызовет ошибку.

Перемещение листа «Лист4» в позицию перед листом, указанным как по порядковому номеру, так и по имени ярлыка:

Удаление листов

Удаление рабочих листов осуществляется с помощью метода Worksheet.Delete

Синтаксис метода Worksheet.Delete

expression.Delete

где expression — переменная, представляющая собой объект Worksheet.

Примеры удаления листов

Если рабочие книги указаны как элементы коллекции Workbooks, в том числе ActiveWorkbook и ThisWorkbook, листы нужно указывать как элементы коллекции Worksheets, использование уникальных имен вызовет ошибку.

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

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

37 комментариев для “VBA Excel. Рабочий лист (создание, копирование, удаление)”

    1. Евгений (автор статьи)

      Привет, Мон!
      Можно удалить все листы, кроме указанных в условии, с помощью цикла For Each … Next:

  1. Если по крупному
    нет Проверки наличия Листа в книге
    нет Переименования Листа

  2. Добрый день! не могу сделать в макросе — чтобы сделанному скопированному листу присваивалось свое имя, которое при новом запуске макроса новому листу будет присваивать новое имя из этой книги (переменное)

    1. Евгений

      Добрый день, Эдуард!
      Я правильно понял вопрос: нужно, чтобы макрос при каждом запуске создавал копию листа и присваивал ему новое имя, отличное от имени по умолчанию? Если так, то по каким правилам должно создаваться новое имя?

  3. Здравствуйте. Не могу найти макрос может кто поможет.
    Задача состоит в том что бы макрос читал все листы книги после чего делал копию этих листов и вставлял в эти же листы данные как значения. Т.е. фактически разрывал связи методом копи паст но на всех листах книги (колличество листов в книге может быть каждый раз разное.

    1. Добрый день!
      У меня вопрос.
      Как перемещать лист перед другим листом, номер или название которого будет вводить пользователь?

      1. Евгений

        Привет, Артур!
        Перемещение листа «Лист2» на позицию перед листом, имя ярлыка которого вводится в текстовое поле информационного окна InputBox:

        1. Спасибо!
          У меня ещё вопрос. А если указывать номер листа, перед которым будет другой лист, какое изменение будет в коде?

          1. Евгений

            А так можно вводить и номер, и имя:

          2. Бладораю.
            У меня последний вопрос.
            Нужно, чтобы при отмене операции по перемещению не было ошибок. Я пробовал с If… Then GoTo, но не выходит.

            1. Евгений

          3. Добрый день! Возможно ли полное копирование листа на нужный мне лист? В январе заполняются данные каждый день и надо чтобы за каждый день создавалась копия на заранее созданные 31 лист по дням?

            1. Евгений

              Добрый день, Эдуард!
              Это можно сделать двумя строками, но перед копированием следует проверить, не был ли исходный лист сегодня уже сохранен под новым именем:

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

                1. Еще вопрос, можно ли скопировать лист не в конец книги а между листами, есть 12 месяцев в книге ексель чтобы копировалось после января, февраля?

                2. И может еще чтобы кнопка с макросом не копировалась а создавалась гипперссылка обратно на лист?

                  1. Евгений

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

                3. Евгений огромное спасибо это то что и требовалось весьма признателен за Вашу помощь!!!

                  1. Евгений

                    Эдуард, представленный выше код будет работать только с одним месяцем. Excel не даст создать в одной книге несколько листов с одним именем (одно и то же число, но разных месяцев). Вам придется как-то изменить наименование листов, например: 1.01-31.01, 1.02-28.02. Тогда для перемещения новых листов придется использовать другой алгоритм или по окончании одного месяца, все его листы переименовать, например так:

                4. Я понял этого и не требуется по окончанию месяца листы будут сохранены отдельно.
                  А вот по предедущему коду нельзя ввести произвольное число т.е не по порядку 1,2,3 а сразу например 17 пропустив с 1 по 16?

                  1. Евгений

                    Можно и так, если указать имя листа, перед которым вставлять перемещаемый (вместо всего блока If … End If):

                5. В таком виде все работает как задумывалось — может кому то пригодится. Спасибо огромное Евгению за помощь!

                6. Помогите с мелочью никак не пойму куда вставить умножить на 2,1 и на 1,9 чтобы не считалось +_ 20% в этой формуле?
                  =ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1;1;1))

                  1. Евгений

                    Эдуард, может быть у вас для ячейки с формулой установлен формат «Процентный»?

                7. Нет числовой. условное форматирование со стрелками вверх вниз и вправо
                  =ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1;1;1*2,6)) для стрелки вверх
                  =ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1;1;1*2,4)) для стрелки вправо
                  но не работает может я не туда вставляю *2,6

                  1. Евгений

                    Синтаксис функции АДРЕС:
                    АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[A1];[имя_листа])
                    Там, где вы умножаете, должно быть логическое значение [A1]: ИСТИНА или ЛОЖЬ (1 или 0).

                8. Александр

                  Ребята привет! Подскажите как решить (буду очень признателен за готовый код) проблему.
                  Есть Гугл таблица с 10 листами. Один лист требуется скачивать на комп в формате CSV — это легко делается самой таблицей — есть возможность сохранить текущий лист. А вот второй лист нужно скачивать в формате ексель. И гугл уже не позволяет скачать текущий лист. Он скачивает в формате ексель всю книгу целиком. Это не удобно. Приходится удалять вручную ненужные листы. Либо сначала копировать весь лист в гугле, а затем вставлять в лист екселя.
                  В общем нужен код, который в дальнейшем привяжу к кнопочке, для скачивания определенного листа из книги с данными без формул и в формате ексель.

                9. удаляю Лист по имени на ярлычке

                  машина вступает в диалог:
                  » Выбранные Листы будут удалены безвозвратно…»
                  Как сделать чтобы машина удаляла и не спрашивала

                    1. Александр

                      Евгений, помогите пожалуйста со следующей задачей.
                      В папке несколько документов эксель, в каждом из которых несколько листов.
                      Необходимо, чтобы макрос открывал по порядку каждый документ, проходил по каждому листу и брал оттуда нужную информацию. Затем эта информация переносится в новый документ эксель в заданном порядке.
                      В целом программу я реализовал, но столкнулся с проблемой, что debug упирается в последний лист первого открытого документа и не может дальше перейти к следующему документу. Т.е. необходимо прописать условие, чтобы vba понимал, что данный лист в документе последний.
                      Подскажите пожалуйста, как это сделать?

                      1. Евгений

                        Александр, для обхода листов каждой книги используйте цикл For Each… Next:

                        Цикл будет завершен после обработки последнего листа.

Добавить комментарий

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