Excel vba обращение к листу: Ссылки на листы по номеру индекса

Содержание

Рабочие листы VBA — voxt.ru

Таблицы Excel VBA

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

В VBA рабочий лист является объектом. Существует два способа обращения к рабочему листу: один с использованием объекта «Рабочий лист», а другой — с использованием объекта «Листы».

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

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

В приведенном выше у нас есть в общей сложности 5 листов. Из этих 5 листов 3 рабочих листа и 2 листа диаграмм.

Здесь «Рабочий лист» количество равно 3, а «Простыни» количество равно 2.

Теперь посмотрите на изображение ниже.

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

Итак, как часть кода, если вы хотите использовать рабочие листы, объекты запоминают этот момент.

Синтаксис рабочих листов VBA

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

Индекс — это не что иное, как номер рабочего листа, на который мы ссылаемся. Как вы можете видеть в конце, он упоминается как Объект.

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

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

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

Как использовать объект рабочих листов в VBA?

Вы можете скачать этот шаблон объекта рабочего листа VBA здесь — Шаблон объекта рабочего листа VBA

Пример №1

Например, предположим, что в вашей рабочей книге всего 5 листов, и названия этих рабочих листов — «Рабочий лист 1», «Рабочий лист 2», «Рабочий лист 3», «Лист диаграммы 1» и «Лист диаграммы 2».

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

Рабочий лист (2). Выбирать означает, что он выберет второй рабочий лист книги.

Код:

Sub Worksheet_Example1()
  Worksheets(2). Select
End Sub

Я запущу этот код с помощью клавиши F5 или вручную и посмотрю результат.

Теперь я изменю номер листа на 3.

Код:

Sub Worksheet_Example1()
  Worksheets(3).Select
End Sub

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

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

Это потому, что я использовал объект Worksheet, а не объект Sheets. Как я уже говорил ранее, объект «Рабочие листы» рассматривает только рабочие листы, а не листы диаграмм.

Чтобы выбрать третий лист из всех листов книги, используйте объект Sheets.

Код:

Sub Worksheet_Example1()

Листы(3).Выбрать

Конец сабвуфера

Теперь он выберет именно третий лист.

Пример № 2. Выбор рабочих листов по имени

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

Код:

Sub Worksheet_Example2()
   Worksheets("Worksheet 3").Select
End Sub

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

Но если вы попытаетесь получить доступ к листу диаграммы с помощью объекта «Рабочие листы», мы получим «Ошибка нижнего индекса вне диапазонаОшибка нижнего индекса вне диапазонаИндекс вне диапазона — это ошибка в VBA, которая возникает, когда мы пытаемся сослаться на что-то или на переменную, которых нет в коде. Например, если у нас нет переменной с именем x, но мы используем функцию msgbox для x, мы получим ошибку нижнего индекса вне диапазона.читать далее».

Код:

Sub Worksheet_Example2()
  Worksheets("Chart Sheet 1").Select
End Sub

Запустите этот код через клавишу F5 или вручную и посмотрите результат.

Пример № 3. Проблема с именем рабочего листа

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

Чтобы решить эту проблему, перейдите в редактор Visual Basic, нажав кнопку ALT + F11 ключ.

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

В этих свойствах окно меняет имя рабочего листа на ваше имя.

Здесь есть одна интересная вещь: несмотря на то, что мы изменили имя рабочего листа с «Рабочий лист 1» на «WS1», мы все еще можем видеть то же имя в рабочей книге.

Теперь мы можем ссылаться на этот лист по имени «WS1».

Код:

Sub Worksheet_Example2()
 
  Worksheets("WS1").Select
End Sub

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

Пример № 4 — Получить общее количество листов в книге

Рабочий лист — это объект, и мы можем использовать все свойства и методы, связанные с ним. Что мы делаем с рабочими листами?

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

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

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

Код:

Sub Worksheet_Example3()
  Dim i As Long
  i = Worksheets.Count
  MsgBox i
End Sub

Это покажет количество рабочих листов.

Несмотря на то, что есть 5 листов, мы получили 3 счета, потому что другие 2 листа являются листами диаграмм.

Чтобы получить общее количество листов, используется объект «Листы».

Код:

Sub Worksheet_Example3()
  Dim i As Long
  i = Sheets.Count
  MsgBox i
End Sub

Это покажет полное количество листов.

Пример № 5 – Методы, использующие объект рабочего листа

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

Добавить новый лист.

Worksheet.Add

Чтобы удалить рабочий лист

Worksheet(“Sheet Name”).Delete

Чтобы изменить имя рабочего листа

Worksheet(“Sheet Name”).Name = “New Name”

УЗНАТЬ БОЛЬШЕ >>

Post Views: 1 215

Как с помощью VBA скопировать данные из одной книги в другую

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

Sub Название_Макроса()
'Выделить диапазон который необходимо скопировать
Range("A1:F52").Select
'Скопировать то, что выделено
Selection.Copy
ChDir "путь к папке где лежит файл в который необходимо скопировать"
Workbooks.Open Filename:= "Название файла, который находится в папке, путь к которой указан выше"
'Выделить начальную ячейку в которую необходимо вставить скопированные данные
Range("A6").Select
'Вставить данные
ActiveSheet.Paste
'сохранить текущую книгу
ActiveWorkbook.Save
'Закрыть книгу
ActiveWorkbook.Close
End Sub

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

Sub Название_Макроса2()
'Открываем файл с которого нужно скопировать данные
Workbooks.Open Filename:="C:\Данные.xlsx"
'Скопировать нужный диапазон в открывшейся книге на листе 1
Workbooks("Данные.xlsx"). Worksheets("Лист1").Range("A16:E16").Copy
'Активируем нужную нам книгу
Workbooks("Книга1.xlsm").Activate
'Выделяем и вставляем скопированные данные в ячейку А1
ActiveWorkbook.Worksheets("Лист1").Range("A1").Select
ActiveSheet.Paste
'Закрываем книгу откуда мы скопировали данные
Workbooks("Данные.xlsx").Close
End Sub

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

Sub Копируем_листы_в_другую_книгу()
Dim bookconst As Workbook
Dim abook As Workbook
Set abook = ActiveWorkbook 'присваиваем перменную активной книге
Set bookconst = Workbooks.Open("C:\Users\User\Desktop\1.xlsx") 'присваиваем перменную книге куда необходимо копировать данные
'переходим в активную книгу откуда необходимо скопировать данные
abook.Worksheets("Лист1").Activate
Range("A1:I23").Copy 'копируем определенный диапазон листа, укажите свой диапазон
bookconst. Worksheets("Лист1").Activate 'активируем лист куда необходимо вставить данные
Range("A1:I23").Select 'встаем на ячейку А1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'вставляем только форматы ячеек
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'второй лист
abook.Worksheets("Лист2").Activate
Range("A1:I23").Copy
bookconst.Worksheets("Лист2").Activate
Range("A1:I23").Select 'выделяем диапазон
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'вставляем только форматы ячеек
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'третий лист
abook.Worksheets("Лист3").Activate
Range("A1:I23").Copy
bookconst.Worksheets("Лист3").Activate
Range("A1:I23").Select 'выделяем диапазон
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'вставляем только форматы ячеек
Selection. PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'сохранить текущую книгу
bookconst.Save
'Закрыть книгу
bookconst.Close
abook.Activate
End Sub

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

Спасибо за внимание.

Объект рабочего листа (Excel) | Microsoft Узнайте

Редактировать

Твиттер

Фейсбук

Эл. адрес

Статья
30.03.2022
3 минуты на чтение

Представляет рабочий лист.

Объект Worksheet является членом коллекции Worksheets . 9Коллекция 0021 Worksheets содержит все объекты Worksheet в книге.

Объект Worksheet также является членом коллекции Sheets . Коллекция Sheets содержит все листы рабочей книги (как листы диаграмм, так и рабочие листы).

Пример

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

 Рабочие листы(1).Visible = False

Индекс рабочего листа обозначает положение рабочего листа на панели вкладок рабочей книги. Рабочие листы(1) — это первый (самый левый) рабочий лист в книге, а Рабочие листы (Рабочие листы.Количество) — последний. Все рабочие листы включаются в подсчет индекса, даже если они скрыты.

Имя рабочего листа отображается на вкладке рабочего листа. Используйте Имя свойство для задания или возврата имени рабочего листа. Следующий пример защищает сценарии на Sheet1.

Dim strPassword как строка
strPassword = InputBox ("Введите пароль для рабочего листа")
Рабочие листы ("Лист1"). Защитить пароль: = strPassword, сценарии: = True

Если рабочий лист является активным листом, для ссылки на него можно использовать свойство ActiveSheet . В следующем примере метод Activate используется для активации Sheet1, установки ориентации страницы в альбомный режим и последующей печати листа.

 Рабочие листы ("Лист1"). Активировать
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.PrintOut

В этом примере используется событие BeforeDoubleClick для открытия указанного набора файлов в Блокноте. Чтобы использовать этот пример, ваш рабочий лист должен содержать следующие данные:

Ячейка A1 должна содержать имена открываемых файлов, разделенные запятой и пробелом.
В ячейке D1 должен быть указан путь к файлам Блокнота.
В ячейке D2 должен быть указан путь к программе «Блокнот».
Ячейка D3 должна содержать расширение файла без точки для файлов Блокнота (txt).

При двойном щелчке по ячейке A1 файлы, указанные в ячейке A1, открываются в Блокноте.

 Private Sub Worksheet_BeforeDoubleClick (цель ByVal как диапазон, отмена как логическое значение)
   «Определите свои переменные.
   Dim sFile как строка, sPath как строка, sTxt как строка, sExe как строка, sSfx как строка
   
   'Если вы не сделали двойной щелчок по A1, то выйдите из функции.
   Если Target.Address <> "$A$1", то выйдите из Sub
   
   'Если вы сделали двойной щелчок на A1, переопределите поведение двойного щелчка по умолчанию с помощью этой функции.
   Отмена = Истина
   
   'Установите путь к файлам, путь к блокноту, расширение файлов и имена файлов,
   'на основе информации на рабочем листе.
   sPath = Диапазон ("D1"). Значение
   sExe = Диапазон ("D2").  Значение
   sSfx = Диапазон ("D3"). Значение
   sFile = Диапазон ("A1"). Значение
   
   'Удалите пробелы между именами файлов.
   sFile = WorksheetFunction.Substitute(sFile, " ", "")
   
   'Пройтись по каждому файлу в списке (через запятую) и
   'создать путь, вызвать исполняемый файл и перейти к следующей запятой.
   Делать пока InStr(sFile, ",")
      sTxt = sPath & "\" & Left(sFile, InStr(sFile, ",") - 1) & "." & sSfx
      Если Dir(sTxt) <> "" Then Shell sExe & " " & sTxt, vbNormalFocus
      sFile = Right(sFile, Len(sFile) - InStr(sFile, ","))
   Петля
   
   'Заканчиваем последнее имя файла в списке
   sTxt = sPath & "\" & sFile & "." & sSfx
   Если Dir(sTxt) <> "" Then Shell sExe & " " & sTxt, vbNormalNoFocus
Конец сабвуфера

События

Активировать
Перед удалением
Перед двойным кликом
ПередПрайткликом
Вычислить
Изменить
Деактивировать
Перейти по гиперссылке
LensGalleryRenderComplete
сводная таблица после изменения значения
Сводная таблицаBeforeAllocateChanges
сводная таблицаBeforeCommitChanges
сводная таблицаBeforeDiscardChanges
Сводная таблицаChangeSync
Обновление сводной таблицы
Изменение Выбора
Обновление таблицы

Методы

Активировать
Вычислить
Объекты диаграммы
Проверка правописания
CircleInvalid
КлирАрроуз
прозрачные круги
Копия
Удалить
Оценить
Экспортасфикседформат
Переместить
OLEОбъекты
Паста
Специальная паста
сводные таблицы
мастер сводных таблиц
Распечатка
Предварительный просмотр
Защита
ResetAllPageBreaks
Сохранить как
Сценарии
Выберите
SetBackgroundPicture
Показать все данные
ШоуДатаформ
Снять защиту
XmlDataQuery
XmlMapQuery

Свойства

Применение
Автофильтр
Режим автофильтра
Ячейки
Круговая ссылка
Кодовое имя
Столбцы
Комментарии
КомментарииThreaded
Функция консолидации
Параметры консолидации
Источники консолидации
Создатель
Пользовательские свойства
DisplayPageBreaks
Отображение справа налево
Включить автофильтр
Включить расчет
EnableFormatConditionsCalculation
EnableOutlining
Включить сводную таблицу
Включение выбора
Режим фильтра
HPageBreaks
Гиперссылки
Индекс
СписокОбъектов
Почтовый Конверт
Имя
Имена
Следующий
Контур
Настройка страницы
Родительский
Предыдущий
PrintedCommentPages
Протектконтентс
Протектдраувингобжектс
Защита
Режим защиты
Защитить сценарии
QueryTables
Диапазон
строк
Область прокрутки
Формы
Сорт
Стандартная высота
Стандартная ширина
Вкладка
TransitionExpEval
TransitionFormEntry
Тип
Используемый диапазон
Видимый
VPageBreaks

См.

также

Справочник по объектной модели Excel

Поддержка и отзывы

У вас есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы Office VBA, чтобы узнать, как вы можете получить поддержку и оставить отзыв.

Получение значений из другого листа в Excel с помощью VBA

Excel чрезвычайно мощен, даже используя только базовые функции добавления данных в ячейки и электронные таблицы, сортировки и обработки этих данных в прекрасную работу клеточного удовольствия. Тем не менее, Excel способен на гораздо большее, чем позволяет стандартное редактирование содержимого ячеек, благодаря волшебству сценариев Visual Basic for Applications или VBA 9.0054 .

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

`Доступ к редактору Visual Basic`

Для начала вам необходимо найти редактор Visual Basic в Excel. Поиск этого будет зависеть от того, какую версию Excel вы используете, но для большинства современных версий Visual Basic Editor можно найти на вкладке Developer ленты меню.

Найдя, просто нажмите Макросы , введите имя макроса (в этом примере мы будем использовать MySum ), затем нажмите создать , чтобы открыть редактор и начать писать сценарии.

`Сценарий`

В редакторе вы увидите объявление макроса MySum , который представляет собой стандартную функцию vba , которую мы можем редактировать в соответствии с нашими требованиями.

 Sub MySum()
Конец сабвуфера

В нашем примере, возможно, у нас есть рабочий лист с именем Sales , и мы хотим использовать этот макрос для суммирования ( sum ) первых 25 значений в столбце B .

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

 Sub MySum()
    Рабочие листы («Продажи»). Диапазон («B1: B25»)
Конец сабвуфера

Теперь нам на самом деле нужно получить общее количество или сумму всех этих значений, поэтому мы заключаем предыдущий вызов объекта в WorksheetFunction of Sum , например так:

 Sub MySum()
    WorksheetFunction.Sum(Worksheets("Продажи").Range("B1:B25"))
Конец сабвуфера

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

Для этого мы будем использовать объект ActiveCell , установив его Value равным нашей сумме:

  Sub MySum()
 ActiveCell.

imacrosoft.ru

Excel vba обращение к листу: Ссылки на листы по номеру индекса

Рабочие листы VBA — voxt.ru

Таблицы Excel VBA

Синтаксис рабочих листов VBA

Как использовать объект рабочих листов в VBA?

Пример №1

Пример № 2. Выбор рабочих листов по имени

Пример № 3. Проблема с именем рабочего листа

Пример № 4 — Получить общее количество листов в книге

Пример № 5 – Методы, использующие объект рабочего листа

Похожие записи

Как с помощью VBA скопировать данные из одной книги в другую

Объект рабочего листа (Excel) | Microsoft Узнайте

Пример

События

Методы

Свойства

См.

Поддержка и отзывы

Получение значений из другого листа в Excel с помощью VBA

`Доступ к редактору Visual Basic`

`Сценарий`