Как создать новый лист для каждого элемента в автофильтре Excel

Одной из наиболее распространенных задач, с которыми сталкивается пользователь Excel, разделение набора данных на отдельные листы. Например, если у вас есть набор данных, который содержит строки для востока, запада, юга и севера региона, вам может быть предложено создать новый лист данных для Востока, новый лист данных для Запада, новый лист для Юга и для Севера. Если вам нужно делать это постоянно, то вы можете использовать этот макрос, чтобы автоматизировать этот процесс.

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

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

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

1. Шаг 1 запускает макрос, объявив пять переменных. MySheet является переменной рабочего листа, которая используется для идентификации листа, в котором хранятся данные автофильтра. MyRange является переменной диапазона, который содержит диапазон основного поля фильтра (поле Регион в данном сценарии). UList является объектом Collection, который помогает нам извлечь уникальные элементы из нашего основного поля фильтра. UListValue служит, как простой счетчик для нашей переменной MyRange. 

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

Вы также можете изменить макрос и явно указать имя листа вместо ActiveSheet, установив MySheet = Sheets(«YourSheetName»). 

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

4. Если макрос достигает Шага 4, значит автофильтр действительно применяется в MySheet. Теперь нам нужно захватить номер столбца, который содержит элементы, которые будут использоваться для анализа наших данных, установленные на отдельные листы. В нашем примере эта область — первый столбец. Таким образом, мы устанавливаем поле MyRange в Columns(1) диапазона фильтра. Это важно! Мы используем указанный столбец, чтобы создать уникальный список элементов, с нашими данными. При реализации этого макроса в вашей работе, вам необходимо изменить номер столбца. 

5. Шаг 5 инициализирует объект UList Collection. Объект Collection представляет собой контейнер, который может содержать массив уникальных элементов данных. На самом деле, объект Collection может содержать только уникальные данные. Если вы пытаетесь заполнить его неоднородными данными, он выдает сообщение об ошибке. Мы используем объект коллекции, чтобы создать уникальный список элементов из нашей переменной MyRange. В этом случае, MyRange указывает на столбец, объект Collection на уникальный список регионов (Восток, Север, Юг, Запад). 

6. Шаг 6 заполняет объект UList коллекции с уникальными элементами данных в MyRange. Для этого он использует переменную I и цикл по строкам столбца MyRange. Вы заметите, что мы начинаем I на 2; это потому, что строка 1 содержит метку заголовка. Мы не хотим включать метку заголовка в качестве одного из уникальных элементов в нашем объекте Collection. 

На каждом цикле, макрос пытается добавить текущую ячейку в коллекции UList. Синтаксис для добавления элемента в коллекции, CollectionName.Add ItemName, UniqueKeyIdentifier

В этом случае мы добавляем каждую ячейку в MyRange как имя элемента и уникального ключа. Поскольку коллекция UList выдает ошибку, если элементы данных не являются уникальными, мы проверяем весь раздел в On Error Resume Next и On Error Goto 0. Это гарантирует, что, если будут добавлены дублирующие элементы, коллекция UList игнорирует их. В конце цикла, у нас есть уникальный список всех элементов данных в MyRange. Опять же, в этом случае, это означает, что у нас есть уникальный список регионов (Восток, Север, Юг, Запад). 

7. Шаг 7 работает исключительно с коллекцией UList. Эта коллекция содержит уникальный список элементов, который мы используем в качестве критериев фильтра и имен листов. Макрос начинает цикл по списку с переменной UListValue. 

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

9. Шаг 9 использует UListValue для фильтрации автофильтра, динамически передавая UListValue в качестве критерия для Field1: MyRange.AutoFilter Field:=1, Criteria1:=UListValue. Число поля здесь очень важно! Поскольку поле Регион является первым полем. При реализации этого макроса, вам необходимо изменить номер поля, чтобы он соответствовал нужному полю. 

10. Каждый объект автофильтра имеет свойство Range. Это свойство Range возвращает строки, к которым применяется автофильтр, то есть он возвращает только те строки, которые отображаются в отфильтрованном наборе данных. Шаг 10 использует метод Copy для захвата вновь отфильтрованных строк и вставки их в новый лист. Обратите внимание, на функцию UListValue. Мы говорим Excel ограничить имя листа слева 31 символами в UListValue. Мы делаем это, потому что предел для имен листов составляет 31 символ. Все, что больше, чем 31 символ выдает ошибку. 

11. Шаг 11 повторяет цикл, чтобы получить следующее значение из коллекции UList. 

12. Макрос заканчивается переходом к исходным данным и очисткой всех фильтров. 

Вам может быть интересно, как создать новую рабочую книгу для каждого элемента в автофильтре. Это относительно легкое изменение. Просто замените код в шаге 10 на этот код. 

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

1. Активируйте редактор Visual Basic, нажав ALT + F11. 
2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта. 
3. Выберите Insert➜Module. 
4. Введите или вставьте код во вновь созданном модуле.