Добавление листа в excel программно

Обновлено: 04.07.2024

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

можно так попробывать

Dim NewSheet As Worksheet
Set NewSheet = Worksheets.Add
NewSheet.Name = "Привет"

А не подскажите дорогие форумчане как создать такой лист с названием которое будет содержать значение ячейки/нескольких ячеек . Например ячейка А1=вася,
С3=пупкин. а лист должен называться "движение вася пупкин". Ну что то в этом роде. и допустим второя штучка это создание именно книги с одним таким листом

Предположим, что в первом рабочем листе активной рабочей книги - находятся все необходимые значения :

With Worksheets
.Add.Name = "Движение " & .Item(1).Range("A1") & " " & .Item(1).Range("C3")
End With

Предположим, что в рабочем листе с именем "Лист1", который наличествует в активной рабочей книге, также находятся значения, которые будут использованы при создании нового рабочего листа :

With Worksheets
.Add.Name = "Движение " & .Item("Лист1").Range("A1") & " " & .Item("Лист1").Range("C3")
End With

Тоже самое, только здесь используется кодовое имя листа, которое может отличаться от имени в семействе Worksheets/Sheets

iNewName$ = "Движение " & Worksheets(1).Range("A1") & " " & Worksheets(1).Range("C3")
Workbooks.Add(xlWBATWorksheet).Worksheets(1).Name = iNewName$

Ознакомиться с ограничениями можно в справке или здесь

Спасибо огромное но есть небольшое "но"

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

iBookName$ = "Движение " & Worksheets(1).Range("A1") & " " & Worksheets(1).Range("C3") & ".xls"
Workbooks.Add.SaveAs Filename:="C:\Мои_документы\" & iBookName$

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

Создание новых рабочих листов осуществляется с помощью метода 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, который для создания диаграмм уже не подойдет.

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

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 Before : = Worksheets ( Worksheets . Count )

Лист1 в After:=Лист1 — это уникальное имя листа, указанное в проводнике редактора VBA без скобок.
Лист1 в After:=Worksheets(«Лист1») — это имя на ярлыке листа, указанное в проводнике редактора VBA в скобках.

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

Set myList = Workbooks ( "Книга1.xlsm" ) . Worksheets . Add

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

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

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

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

expression.Copy [Before, After]

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

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

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

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

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

Worksheets ( "Лист1" ) . Copy Before : = Worksheets ( "Лист2" ) Workbooks ( "Книга1.xlsm" ) . Worksheets ( "Лист1" ) . Copy _

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

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

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

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

expression.Move [Before, After]

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

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

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

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

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

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

Worksheets ( "Лист1" ) . Move Before : = Worksheets ( "Лист2" ) Workbooks ( "Книга1.xlsm" ) . Worksheets ( "Лист1" ) . Move _

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

x = InputBox ( "Введите имя или номер листа" , "Перемещение листа «Лист4»" )

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

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

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

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

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

Workbooks ( "Книга1.xlsm" ) . Worksheets ( "Лист1" ) . Delete

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

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

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

А как удалить листы не указывая постоянно их название?:

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

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

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

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

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

x = InputBox ( "Введите имя листа" , "Перемещение листа «Лист2»" )

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

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

x = InputBox ( "Введите имя или номер листа" , "Перемещение листа «Лист2»" ) x = InputBox ( "Введите имя или номер листа" , "Перемещение листа «Лист2»" )

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

. PasteSpecial Paste : = xlPasteFormulasAndNumberFormats

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

MsgBox "Лист за " & FormatDateTime ( Date , vbShortDate ) & " уже сохранен!" Worksheets ( "Січень" ) . Copy After : = Worksheets ( Worksheets . Count ) 'присваиваем листу в качестве имени число текущей даты

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

destSht . Move After : = Worksheets ( Worksheets . Count )

Спасибо все работает!

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

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

Sheets . Add . Name = InputBox ( "Ввести дату - від 1 до 31" ) destSht . Move After : = Worksheets ( Worksheets . Count )

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

Sheets . Add . Name = InputBox ( "Ввести дату - від 1 до 31" ) destSht . Move After : = Worksheets ( CStr ( CByte ( destSht . Name ) - 1 ) )

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

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

MsgBox "Такой номер уже используется - введите другой"

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

Sheets . Add . Name = InputBox ( "Ввести дату - від 1 до 31" )

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

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

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

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

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

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

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

Здравствуйте!
Возможно ли при копировании листа из другого файла перенести и пользовательский стиль таблицы?

Добрый день!
Если не ошибаюсь, пользовательский стиль таблицы всегда копируется вместе с листом, но внешний вид таблиц будет одинаков, если оба файла созданы и открыты в одной версии Excel.

Спасибо за ответ.
Увы, не так, и даже всё не так.
Стиль «пытается» копироваться, цвета отличаются оттенками, но это полбеды. Главное, что нужно мне, чтобы копировался цвет шрифта таблицы, а этого не происходит. Почему-то на листе-цели устанавливается на весь лист чёрный шрифт, который Эксель считает главнее табличного.
И даже больше. После ручной замены на всё листе-цели шрифта с чёрного на авто замены цвета шрифта в импортированной таблице не происходит. Что всегда есть при ручной работе с таблицами.
Ну, и совсем глюк. Пользовательский стиль переезжает и доступен в новой книге. Но даже при ручном его выборе на листе-цели для таблицы не происходит смены цвета шрифта.
Цвет можно поменять только руками (макросом) без учёта таблицы.
Всё это происходит на одном компьютере в одной ОС В10.

Хм. Эксель 2016, всё выше описанное по-прежнему.
Если не затруднит, скопируйте умную таблицу макросом на новый лист, перед этим созданный тем же макросом.

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

1. Подключение к Excel

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

2. Запись значения в ячейку

Количество листов книги

3. Шрифт и размер шрифта

4. Ширина колонки

Чтобы скрыть колонку, нужно установить ширину в значение 0.

Получение последней ячейки в используемом диапазоне

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

4. Установка фильтра

5. Горизонтальное выравнивание ячейки

Режим выравнивания

Константа в Excel

Значение в ISBL

По центру выделения

По правому краю

6. Вертикальное выравнивание ячейки

Режим выравнивания

Константа в Excel

Значение в ISBL

По нижнему краю

По верхнему краю

7. Установка свойства ячейки "переносить по словам"

8. Отключение/включение режима показа предупреждений

9. Формулы

Для формул с русскими названиями нужно использовать не Formula, а FormulaLocal. Таким образом правильными вариантами написания формул будут:

10. Таблица цветов с индексами

11. Установка границы ячейки и тип линии границы

Значения константы XlBordersIndex

Значение в Excel

Значение в ISBL

В виде тире и точек

В виде тире и двойных точек

В виде двойной линии

В виде наклонной пунктирной

Свойство LineStyle (тип линии) может принимать следующие значения

Расположение линии

Значение в Excel

Значение в ISBL

Линия по диагонали сверху – вниз

Линия по диагонали снизу – вверх

Линия, обрамляющая диапазон слева

Линия, обрамляющая диапазон сверху

Линия, обрамляющая диапазон снизу

Линия, обрамляющая диапазон справа

Все вертикальные линии внутри диапазона

Все горизонтальные линии внутри диапазона

Значения константы XlBordersIndex

Расположение линии	Значение в Excel	Константа в ISBL
Линия по диагонали сверху вниз	xlDiagonalDown	5
Линия по диагонали снизу – вверх	xlDiagonalUp	6
Линия, обрамляющая диапазон слева	xlEdgeLeft	7
Линия, обрамляющая диапазон сверху	xlEdgeTop	8
Линия, обрамляющая диапазон снизу	xlEdgeBottom	9
Линия, обрамляющая диапазон справа	xlEdgeRight	10
Все вертикальные линии внутри диапазона	xlInsideVertical	11
Все горизонтальные линии внутри диапазона	xlInsideHorizontal	12

Свойство LineStyle (тип линии) может принимать следующие значения:

Тип линии	Значение в Excel	Константа в ISBL
Нет линии	xlLineStyleNone	0
Непрерывная	xlContinuous	1
В виде тире и точек	xlDashDot	4
В виде тире и двойных точек	xlDashDotDot	5
В виде точек	xlDot	8
В виде двойной линии	xlDouble	9
В виде наклонной пунктирной	xlSlantDashDot	13

Получение порядкового номера колонки Excel по буквенному обозначению имени

Однажды столкнулся с тем, что нужно было получить буквенное обозначение колонки Excel по её порядковому номеру. Например, для колонки номер 3 буквенное обозначение равно "C". Так вот ниже приведены две замечательные функции, которые вполне справляются с решением данной проблемы.

Наоборот, получение буквенного обозначения имени колонки по номеру колонки

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

Выгрузка данных в Excel

Для вывода (выгрузки) данных в Excel необходимо либо открыть существующую книгу, либо создать новую, и выбрать рабочий лист для вывода данных.
Для создания новой книги можно использовать следующий код:
При создании книги автоматически создаются листы (по умолчанию 3). Нам остается только выбрать нужный:
Или добавить в книгу новый лист:
Добавим в ячейку на листе значение:
Запишем книгу:
Где ПутьКФайлу - полный путь к файлу книги Excel (включая имя).

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

Часто используемые методы Excel

Эксель.Visible = Видимость;	0 - Excel не виден, 1 - виден.
Книга = Эксель.WorkBooks.Add();	Создание новой книги (файла) Excel.
Книга.SaveAs(ИмяФайла);	Сохранение книги Excel.
Лист = Книга.WorkSheets.Add();	Добавление нового листа в книгу.
Книга = Эксель.WorkBooks.Open(ИмяФайла);	Открытие существующей книги (файла) Excel.
Лист = Книга.WorkSheets(НомерЛиста);	Установка листа в качестве рабочего с номером НомерЛиста.
Лист.Name = ИмяЛиста;	Задание рабочему листу имени ИмяЛиста
Лист.PageSetup.Zoom = Масштаб;	Задание параметра страницы "Масштаб" (от 10 до 400).
Лист.PageSetup.Orientation = Ориентация;	Ориентация: 1 - книжная, 2 - альбомная.
Лист.PageSetup.LeftMargin = Эксель.CentimetersToPoints(Сантиметры);	Задание левой границы (в сантиметрах).
Лист.PageSetup.TopMargin = Эксель.CentimetersToPoints(Сантиметры);	Задание верхней границы (в сантиметрах).
Лист.PageSetup.RightMargin = Эксель.CentimetersToPoints(Сантиметры);	Задание правой границы (в сантиметрах).
Лист.PageSetup.BottomMargin = Эксель.CentimetersToPoints(Сантиметры);	Задание нижней границы (в сантиметрах).
Лист.Columns(НомерКолонки).ColumnWidth = Ширина;	Задание ширины колонке.
Лист.Cells(НомерСтроки, НомерКолонки).Value = Значение;	Ввод данных в ячейку.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Name = ИмяШрифта;	Установка шрифта в ячейке.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Size = РазмерШрифта;	Установка размера шрифта в ячейке.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Bold = Жирный;	1 - жирный шрифт, 0 - нормальный.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Italic = Курсив;	1 - наклонный шрифт, 0 - нормальный.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Underline = Подчеркнутый;	2 - подчеркнутый, 1 - нет.
Лист.Cells(НомерСтроки, НомерКолонки).NumberFormat = Формат;	Установка формата данных ячейки.
Лист.Cells(НомерСтроки,НомерКолонки).Borders.Linestyle = ТипЛинии;	Установка рамок ячейки. 1 - тонкая сплошная.
Лист.Protect();	Установка защиты на лист
Лист.UnProtect();	Снятие защиты с листа
Лист.Cells(Строка, Столбец).Locked=0;	Ячейка будет доступной (и после установки защиты на лист)

Хитрости Excel

Как выборочно разрешить / запретить редактирование ячеек листа

Как осуществить поиск / замену значений ячеек на листе

Замечание. Текст для замены лучше выделять, например, в угловые скобки, аналогично параметрам макета в 1С. Это улучшит наглядность, визуально отделит от рабочего текста. Текст для поиска в этом случае будет выглядеть так: ТекстДляПоиска

Читайте также: