0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

**Метод Worksheets.Add можно использовать для создания рабочего листа (xlWorksheet), который больше не подходит для построения диаграммы.

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

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

Вы можете связать объектную переменную с создаваемым листом:

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

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

Рабочие листы копируются с использованием метода Worksheet.Copy.

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

Выражение.копия [до, после]

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

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

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

При отсутствии «До» и «После» Excel создаст новую книгу и поместит в нее копию листа. Он будет перенесен в новую книгу, если скопированный лист содержит код VBA (в модуле листа).

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

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

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

Перемещайте рабочие листы с помощью кнопки Рабочий лист. Метод перемещения.

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

Выражение.движение [до, после].

Объект рабочего листа представляется выражением, переменной.

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

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

При отсутствии кнопок До и После Excel автоматически создаст новую книгу и вставит в нее лист.

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

Простые примеры шевелящихся листьев:

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

Читайте так же:
Как в Excel добавить символ перед каждым словом в ячейке?

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

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

Чтобы удалить рабочие листы, используйте функцию Рабочий лист. Метод удаления

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

Где выражение — переменная, представляющая объект рабочего листа.

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

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

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

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

Можно ли удалять листы, не называя их постоянно? :

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

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

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

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

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

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

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

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

Это позволит вам ввести как номер, так и имя:

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

Спасибо. Могу ли я скопировать содержимое листа на новый лист, который мне нужен? Мне нужно создать копию листа 31 день для каждого дня в январе, чтобы данные заполнялись каждый день?

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

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

Читайте так же:
Как автоматически скрыть строки, если в столбце пустые ячейки?

Спасибо. Все работает!

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

Также, возможно, чтобы кнопка с макросом не создавала гиперссылку обратно в список при копировании?

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

Вы мне очень помогли, Евгений. Спасибо вам большое.

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

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

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

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

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

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

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

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

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

Читайте так же:
Как быстро перейти к первому / последнему / конкретному листу в Excel?

Удалить лист по его имени из ярлыка

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

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

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

Процесс будет завершен после того, как будет обработан последний лист.

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

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

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

Я провел тест: создал умную таблицу, создал пользовательский стиль и применил его к таблице. Далее изменил у разных блоков строк цвет шрифта и начертание (полужирное, курсив). Программно скопировал лист с таблицей в другую книгу. Все форматирование сохранилось и пользовательский стиль тоже появился в разделе со стилями таблиц. У меня версия Excel — 16.0 (Excel 2016). Оба файла должны быть созданы в одной версии Excel, так как более старые версии Excel могут не поддерживать возможности новых версий.

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

Работайте легко!

С Kutools для Excel в считанные секунды и без особых навыков Вы сможете:

  • Создавать красивые выпадающие списки;
  • Легко анализировать данные;
  • Создавать списки из файлов Windows с гиперссылками (листы документов, файлы и т.д.). );
  • Быстро создавать папки в файловой системе из списка в Excel;
  • Создавать списки листов рабочей книги Excel;
  • Делать красивые нестандартные таблицы и диаграммы;
  • Быстро объединять таблицы;
  • Мгновенно сравнивать столбцы и колонки;
  • Находить ошибки и дубликаты в данных;
  • Объединять ячейки Excel без потери данных из обеих ячеек;
  • Шифровать ячейки Excel с помощью пароля;
  • Быстро менять местами ячейки или диапазоны;
  • Конвертировать физические величины и валюты;
  • Выполнять вычисления для затененных цветом ячеек;
  • Мгновенно выбирать ячейки по условиям, формату, содержимому и т.д.
Читайте так же:
Как быстро вставить уникальные порядковые номера в Excel?

Содержание справочника по функциям Kutools для Excel:

Блок инструментов «Вид»

Панель Навигации:

Она содержит 5 вкладок: «Книги и листы», «Автоматический текст», «Менеджер имен», «Менеджер столбцов» и «Найти и заменить».

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

Подсветка области курсора

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

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

Панель Формул

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

Временные снимки (Резервная Копия Текущей таблицы)

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

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

Столбцы

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

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

Читайте так же:
Как автоматически вставить строку на основе значения ячейки в Excel?

Листы

Быстрое изменение видимости всех скрытых листов в текущей книге.

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

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

> Рабочая область
  • 1. Закройте ленту и скройте строку состояния
  • 2. Закройте ленту, скройте строку состояния и строку формул
  • 3. Закройте ленту, скройте строку состояния, строку формул и ленту.
> Всегда Показывать Комментарий

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

> Установить рабочую область/Отобразить всю книгу:

Это фантастический способ установить или ограничить область просмотра таблицы. Если вы много перемещаете свой лист или у вас есть данные, которые вы не хотите показывать читателям, этот инструмент удобно ограничит видимую область листа. Вы можете быстро отобразить все скрытые диапазоны на рабочем листе Excel кликнув на кнопку «Отобразить всю книгу».

> Скрыть Листы кроме текущего:

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

> Показать Все Скрытые Листы:

Отображение всех скрытых листов в активной рабочей книге одним щелчком мыши. Обычно в Excel следует отображать все скрытые листы по одному.

> Скрыть Неактивные Книги:

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

> Показать Все Скрытые Книги:

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

> Скрыть/Отобразить Рабочие Книги и Листы:

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

> Опции отображения:

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector