Создаем отдельный Excel-файл для каждого значения фильтра

Сводные таблицы - отличный механизм для анализа больших наборов данных. Вы можете создать отчет, а затем поместить поле (например, регион) в фильтр отчета. Вы можете выбрать каждый элемент данных по очереди, а затем создать новый файл для каждого варианта сводной таблицы. Этот макрос позволяет автоматически перебрать все значения в фильтре отчета и создать новый файл для каждого значения фильтра.

В объектной модели Excel, Фильтр сводной таблицы - PageField. Для создания файла со сводной таблицей для каждого элемента, мы должны перебрать коллекцию PivotItems объекта PageField. Во время цикла мы динамически меняем выбор в фильтре отчета, а затем экспортируем отчет сводной таблицы в новую книгу.

1. Шаг 1 объявляет две переменные объекта, используя Pt в качестве контейнера памяти для сводной таблицы и Pf в качестве контейнера для памяти наших полей данных. Это позволяет макросу перебрать все поля данных в сводной таблице. 

2. Шаг 2 присваиваем активной сводной таблице переменную РТ. Мы делаем это, используя свойство ActiveCell.PivotTable.Name, чтобы получить имя целевого диапазона. Если активная ячейка не находится внутри сводной таблицы, выдается ошибка. Именно поэтому макрос использует On Error Resume Next Statement. Это говорит Excel продолжить макрос, если есть ошибка. 

3. Шаг 3 проверяет переменную РТ - если она пустая, то выводим пользователю сообщение о том, что необходимо поместить курсор в сводную таблицу и выходим из процедуры.

4. Шаг 4 определяет, сколько полей в фильтре (если количество PageFields больше единицы, значит в сводной таблице более одного фильтра). Мы делаем эту проверку, чтобы избежать печати отчетов для остальных фильтров. Без этой проверки вы можете печатать сотни страниц. Макрос останавливается, если количество полей в фильтре больше 1. Вы можете удалить это ограничение. 

5. Шаг 5 запускает два цикла. Первый перебирает все фильтры отчета. Второй проходит по всем элементам фильтра, который в настоящее время выбран. 

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

7. Шаг 7 копирует TableRange1 (встроенный объект — диапазон, который указывает на диапазон основной области данных для сводной таблицы). Затем вставляет данные в новую книгу и сохраняет её. Обратите внимание, что вам нужно изменить путь сохранения. 

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

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

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