Что делает макрос: Сводные таблицы обеспечивают отличный механизм для синтаксического анализа больших массивов данных в отдельные файлы. Вы можете создать отчет сводной таблицы, в комплекте, а затем поместить поле (например, область) в фильтр отчета. С помощью фильтра отчетов вы можете выбрать каждый элемент данных один за одним, а затем экспортировать данные сводной таблицы в новую книгу.
Макрос в этом разделе показывает, как автоматически перебирать все значения в отчете фильтра и экспортировать в новую книгу.
Как макрос работает
В объектной модели Excel раскрывающийся список «Фильтр отчетов» называется PageField. Чтобы распечатать Сводную таблицу для каждого элемента данных в фильтре отчета, макрос должен циклически перемещаться по сводным элементам коллекция объекта PageField. Поскольку макрос зацикливается, он должен динамически изменять выбор в фильтр отчета, а затем экспортируйте отчет сводной таблицы в новую книгу.
Код макроса
Sub NoviiFailDlyaKajdogoElementaFiltra()
'Шаг 1: Объявляем переменные
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
'Шаг 2: Курсор в активной ячейке в сводной таблице
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
'Шаг 3: Выход, если активная ячейка не в сводной таблице
If pt Is Nothing Then
MsgBox "Вы должны поместить курсор в сводную таблицу."
Exit Sub
End If
'Шаг 4: Выход, если более одного поля страницы
If pt.PageFields.Count > 1 Then
MsgBox "Слишком много полей фильтра отчетов. Предел 1."
Exit Sub
End If
'Шаг 5: Начните цикл по полю страницы и его элементам поворота
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
'Шаг 6: Изменить выбор в фильтре отчетов
pt.PivotFields(pf.Name).CurrentPage = pi.Name
'Шаг 7: Скопируйте область данных в новую книгу
pt.TableRange1.Copy
Workbooks.Add.Worksheets(1).Paste
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs _
Filename:="C:\Temp\" & pi.Name & ".xlsx"
ActiveWorkbook.Close
Application.DisplayAlerts = True
'Шаг 8: Получить элемент поля следующей страницы
Next pi
Next pf
End Sub
Как этот код работает
- Шаг 1 объявляет две переменные объекта, используя Pt в качестве контейнера памяти для сводной таблицы и Pf в качестве контейнера для памяти наших полей данных. Это позволяет макросу перебрать все поля данных в сводной таблице.
- На шаге 2 мы устанавливаем переменную Pt на имя сводной таблицы, на которой найдена активная ячейка. Мы делаем это, используя свойство ActiveCell.PivotTable.Name, чтобы получить имя целевого диапазона.
- Шаг 3 проверяет, заполнена ли переменная PT сводной таблицы объекта. Если переменная PT установлена в Nothing, активная ячейка была не на сводной таблице, таким образом, сводной таблице не может быть присвоена переменная. Если дело обстоит именно так, то макрокоманда помещает окно сообщения, чтобы уведомить пользователя, а затем выходит из процедуры.
- Шаг 4 определяет есть ли еще поле фильтра отчета. Если количество PageFields больше единицы, существует более одного фильтра отчета. Причина, почему мы делаем эту проверку проста. Мы хотим, избежать печати отчетов для фильтров, которые просто оказались там. Без этой проверки, вы можете закончить печать сотен страниц. Макрос останавливается и отображает окно сообщения, если количество поля больше 1. Вы можете удалить ограничение, если вам нужно просто удалив Шаг 4 в макрос.
- Шаг 5 запускает два цикла. Внешний контур говорит Excel, чтобы перебрать все фильтры отчетов. Внутренний цикл говорит Excel пойти по всем элементам сводной в докладе
фильтра, который в настоящее время имеет фокус. - Для каждого элемента сводной, Шаг 6 захватывает имя элемента и использует его для изменения выбора отчета фильтра. Это эффективно изменяет отчет сводной таблицы для соответствия пункта сводной.
- Шаг 7 копий TableRange1 сводной таблицы объекта. TableRange1 является встроенным объектом — диапазон, который указывает на диапазон основной области данных для сводной таблицы. Затем вставляет данные в новую книгу и сохраняет его. Обратите внимание, что вам нужно изменить путь сохранения к тому, который работает в вашей среде.
- Шаг 8 переходит к следующему пункту сводной. После того, как макрос проходит через все пункты сводной в фильтре отчета, Макрос переходит к следующему PageField. После того, как все PageFields были оценены, Макрос завершен.
Как использовать
Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:
- Активируйте редактор Visual Basic, нажав ALT + F11.
- Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
- Выберите Insert➜Module.
- Введите или вставьте код.