Vba excel порядок листов

Обновлено: 05.07.2024

В Word ниже объекта Application и Document начинались уже объекты непосредственно для работы с текстом — Selection, Range и т.п. В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект — объект Worksheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.

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

Процесс создания выглядит очень просто:

Dim oExcel As New Excel.Application 'Запускаем Excel

oExcel.Visible = True 'Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add () 'Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Add() ' Создаем новый лист

oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"

Метод Add() для коллекции Worksheets принимает несколько необязательных параметров, главная задача которых — определить, между какими существующими листами будет вставлен новый лист. Если ничего не указывать, то новый лист будет помещен самым первым.

Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем "Лист1" и переименовываем его в "Новый лист":

Dim oExcel As New Excel.Application 'Запускаем Excel

oExcel.Visible = True 'Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add() 'Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Item("Лист 1") ' Находим Лист1

oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"

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

У коллекции Sheets, помимо привычных нам свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее применять для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку все равно указывать конкретный лист — есть и один специфический метод FillAcrossSheets() — скопировать объект диапазона Range (варианты: полностью, только содержимое, только оформление) во все листы данной книги.

У объекта Worksheet — множество важных свойств и методов:

Cells — одно из наиболее часто используемых свойств объекта Worksheet. Работает точно так же, как и рассмотренное выше одноименное свойство объекта Application — за исключением того, что вам больше не нужно ограничиваться только активным листом. Аналогично работают свойства Columns и Rows.
EnableCalculation — возможность отключить автоматический пересчет значений ячеек в книге.
EnableSelection — возможность запретить выделять на листе: все, ничего не запрещать, или разрешить выделять только незаблокированные ячейки.
Next — возможность получить ссылку на следующий лист в книге. Previous — то же самое для предыдущего листа.
PageSetup — как и в Word, возможность получить объект PageSetup, при помощи которого можно настроить те же параметры, что и через меню Файл -> Параметры страницы.
свойство Protection позволяет получить объект Protection, при помощи которого можно запретить пользователю вносить изменения в лист Excel. Настройке параметров защиты также служат и другие свойства, названия которых начинаются на Protection.
QueryTables — исключительно важное свойство. Оно возвращает коллекцию QueryTables — набор объектов QueryTable, которые, в свою очередь, представляют данные, полученные из внешних источников (как правило, из баз данных).
Range — самое важное свойство объекта Worksheet. Возвращает объект Range (диапазон ячеек), который в объектной модели Excel занимает примерно такое же место, что и одноименный объект в объектной модели Word. Этот объект будет рассматриваться ниже.
Type — возможность определить тип данного листа. Обычно используются два типа: xlWorksheet (обычный лист) и xlChart (диаграмма).
UsedRange — возвращает объект Range, представляющий собой прямоугольную область, включающую все непустые ячейки. Удобно для целей копирования или форматирования.
Visible — возможность спрятать лист с глаз пользователя (например, если он используется для служебных целей).

Некоторые важные методы объекта Worksheet:

методы Activate() , Calculate(), Copy(), Paste(), Delete(), Move(), Evaluate(), Select(), SaveAs(), PrintOut(), PrintPreview(), Protect(), Unprotect()нам уже знакомы . Отличие заключается только в том, что теперь эти методы могут применяться для выбранного вами листа.
метод PivotTables() возвращает коллекцию очень интересных объектов PivotTable (сводная таблица), которые будут рассматриваться ниже;
метод Scenarios() возвращает коллекцию Scenarios, состоящую из объектов Scenario (сценарии). Сценарии — это именованные наборы вводных данных, которые можно использовать для проверки различных сценариев (разные суммы продаж, уровни налогов, расходов и т.п.)
SetBackgroundPicture() — возможность назначить листу фоновое изображение (естественно, желательно, чтобы оно было полупрозрачное — "водяной знак", иначе на его фоне будет трудно читать текст в ячейках).
ShowAllData() — показать все скрытые и отфильтрованные данные на листе.

Самое важное событие объекта Worksheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения ячейки должно приводить к изменению значения в ячейке другого листа/рабочей книги Excel или даже в базе данных. Другая ситуация, в которой используется это события — сложная проверка вводимого пользователем значения (например, опять-таки через обращение к базе данных). Эта событийная процедура работает со специальным параметром Target — то есть объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.

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

Сортировка листов в алфавитном / буквенно-цифровом порядке с кодом VBA

В центре поддержки Майкрософт есть макрос для сортировки листов по альфа-каналу. Мы можем применить его, выполнив следующие шаги:

1. Удерживая нажатой ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.

2. Нажмите на Вставить > Модуль, а затем вставьте следующий макрос в Окно модуля.

VBA: сортировка листов в алфавитном / буквенно-цифровом порядке

3. нажмите F5 ключ для запуска этого макроса. В следующем окне подсказки щелкните Да, все листы будут отсортированы по возрастанию в алфавитном порядке; и нажмите Нет, все листы будут отсортированы по убыванию в алфавитном порядке.

Сортировка листов в алфавитном / буквенно-цифровом порядке с помощью Kutools for Excel

Если вы не знакомы с макросами или предпочитаете другие способы, вы можете попробовать Kutools for Excel. Kutools for Excel's Сортировать листы инструмент может легко сортировать все листы.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Нажмите Kutools Plus > Рабочий лист > Сортировать листы. Смотрите скриншот:

2. В Сортировать листы В диалоговом окне выберите один тип сортировки, который вам нужен на правой панели, например Альфа-сортировка, Буквенно-цифровая сортировка, а затем нажмите OK кнопка. Смотрите скриншот:

Затем все листы сортируются на основе указанного типа сортировки. Смотрите скриншот:

Kutools for Excel's Сортировать листы инструмент может быстро переупорядочить все листы в активной книге. Он поддерживает несколько типов сортировки, в том числе Альфа-сортировка, Буквенно-цифровая сортировка, Цвет Сортировка и Обратный. Кроме того, вы также можете перемещать листы вверх / вниз и сбрасывать сортировку.

Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Существуют разные способы сортировки данных в Microsoft Excel. Под вкладкой Excel Data находится значок сортировки, откуда вы можете быстро отсортировать данные и получить результаты. Почему тогда нужно писать сложный код VBA?

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

VBA имеет метод Range.Sort для сортировки данных. Где Range указывает диапазон ячеек, которые мы хотим отсортировать в порядке возрастания или убывания.

Синтаксис для Range.Sort приведен ниже:

Ключ - столбец / диапазон, который нужно отсортировать. Ex. Если вы хотите отсортировать ячейки A1: A10, вы должны упомянуть Range (A1: A10)
Порядок - это параметр, который позволяет сортировать данные в порядке возрастания или убывания.
Заголовок - это параметр, который указывает, имеет ли ваш столбец / диапазон заголовки или нет.

Этих трех параметров достаточно для нашей работы. Однако есть некоторые другие параметры, такие как MatchCase, SortMethod и т. Д., Которые вы всегда можете изучить и посмотреть, как они работают.

Как использовать функцию сортировки Excel VBA?

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

Вы можете скачать этот шаблон VBA Sort Excel здесь - Шаблон VBA Sort Excel

Функция сортировки VBA - пример № 1

Сортировка одного столбца без заголовка

Предположим, у вас есть столбец с именами, как показано ниже, и все, что вам нужно, это отсортировать эти данные в алфавитном порядке в порядке возрастания или убывания.

Выполните следующие шаги, чтобы использовать функцию сортировки в VBA.

Шаг 1: Определите новую sup-процедуру в модуле и создайте макрос.

Код:

Шаг 2: Используйте функцию Range.Sort для сортировки этого столбца в порядке возрастания.

Код:

Здесь вы даете диапазон, начиная с ячейки A1 до последней использованной / непустой ячейки (см. Функцию .End (xlDown)) для функции Range.Sort.

Шаг 3: Теперь введите значения аргумента.

Код:

Как мы уже обсуждали ранее, Key, Order и Header являются важными и необходимыми аргументами, которые необходимо предоставить. В качестве начального диапазона столбца мы указали Range («A1»), который нам нужно отсортировать. Порядок предоставляется в порядке возрастания, а заголовок - как нет (что означает, что столбец не имеет заголовка).

Этот код проверяет все непустые ячейки, начиная с A1, а затем сортирует их в порядке возрастания, начиная с ячейки A1.

Шаг 4: Запустите этот код, нажав F5 или кнопку Run вручную и посмотрите результат.

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

Функция сортировки VBA - пример № 2

Сортировка одной колонки с заголовком

Предположим, у вас есть столбец с заголовком, как показано ниже. И вы хотели отсортировать этот столбец в порядке возрастания или убывания.

Выполните следующие шаги, чтобы использовать функцию сортировки в VBA.

Шаг 1: Определите новую подпроцедуру в новой модели для хранения макроса.

Код:

Шаг 3: Используйте Range («A1»). Сортируйте перед приведенной выше строкой кода, чтобы сделать ее функцией сортировки.

Код:

Шаг 4: Введите Key1 как Range («A1») для сортировки данных из ячейки A1, Order1, чтобы отсортировать данные в порядке возрастания или убывания, и Header как « Да», чтобы система знала, что первая строка является заголовком в вашем данные.

Код:

Шаг 5: Запустите этот код, нажав F5 или кнопку Run вручную и посмотрите результат.

Здесь данные из примера № 2 данной рабочей книги Excel сортируются в порядке возрастания, учитывая, что они имеют заголовок. Это означает, что при сортировке этих данных первая строка (которая содержит имя Emp ) игнорируется, поскольку она рассматривается как заголовок для этих данных в столбце A.

Вы также можете отсортировать те же данные в порядке убывания алфавитов. Все, что вам нужно сделать, это изменить порядок с возрастания на убывание.

Шаг 6: Измените order1 на нисходящий, чтобы отсортировать данные в порядке убывания.

Код:

Шаг 7: Запустите код и посмотрите вывод, как показано ниже.

Вы можете видеть, что данные отсортированы в порядке убывания.

Функция сортировки VBA - пример № 3

Сортировка нескольких столбцов с заголовками

До сих пор мы рассмотрели, как сортировать данные одного столбца в порядке возрастания или убывания (без заголовка и с заголовком). Что если у вас есть данные, которые нужно отсортировать по нескольким столбцам? Можно ли написать код для того же?

Ответ: «Да, конечно, это можно сделать!»

Предположим, у вас есть данные, указанные ниже:

Вы хотели отсортировать эти данные сначала по Emp Name, а затем по Location. Выполните следующие шаги, чтобы увидеть, как мы можем кодировать его в VBA.

Шаг 1: Определите новую подпроцедуру для добавления макроса в новый модуль.

Код:

Шаг 2: Используйте оператор With… End With, чтобы добавить несколько условий сортировки в одном цикле.

Код:

Шаг 3: Теперь используйте SortFields.Add, чтобы добавить несколько условий сортировки в одном листе.

Код:

Шаг 4: Определите диапазон листов для сортировки и заголовок На следующем шаге.

Код:

Шаг 5: Используйте .Apply, чтобы применить все это под оператором with и закрыть цикл, пишущий End With.

Код:

Шаг 6: Запустите этот код, нажав F5 или кнопку Run вручную, и увидите результат.

В этом коде ActiveSheets.Sort помогает системе идентифицировать лист, по которому должны быть отсортированы данные. SortFields.Add позволяет добавлять два условия сортировки с их порядком (по возрастанию в обоих случаях). SetRange позволяет системе установить диапазон от A1 до C13. Вы также можете увеличить этот диапазон. Операторы Apply позволяют системе применять все изменения, сделанные в цикле With.

Наконец, вы получите данные, которые отсортированы по имени Emp сначала, а затем по местоположению.

То, что нужно запомнить

Под сортировкой VBA вы можете создавать именованные диапазоны вместо ссылок на ячейки и использовать их. Ex. Если вы создали именованный диапазон для ячейки A1: A10 как «EmpRange», вы можете использовать его в Range.Sort, например Range («EmpRange»).
Вы можете сортировать данные как по возрастанию, так и по убыванию, как в Excel.
Если вы не уверены, есть ли у ваших данных заголовок или нет, вы можете использовать xlGuess в разделе заголовка, чтобы система могла угадать, является ли первая строка данных заголовком или нет.