Как создать список всех сводных таблиц книги в Excel

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

Как макрос работает

Когда вы создаете переменную объекта PivotTable, вы открываете все свойства сводной таблицы — такие как имя, местоположение, индекс кэша и т. д. В этом макросе цикл проходит по каждой сводной таблице, извлекает определенные свойства на новый лист.

Код макроса

Sub SpisokSvodnihTablicKnigi()
'Шаг 1: Объявляем переменные
Dim ws As Worksheet
Dim pt As PivotTable
Dim MyCell As Range
'Шаг 2: Добавляем новый лист с заголовками столбцов
Worksheets.Add
Range("A1:F1") = Array("Pivot Name", "Worksheet", _
"Location", "Cache Index", _
"Source Data Location", _
"Row Count")
'Шаг 3: Устанавливаем якорь курсора в ячейке А2
Set MyCell = ActiveSheet.Range("A2")
'Шаг 4: Запускаем цикл по листам книги
For Each ws In Worksheets
'Шаг 5: Запускаем цикл по всем сводным таблицам
For Each pt In ws.PivotTables
MyCell.Offset(0, 0) = pt.Name
MyCell.Offset(0, 1) = pt.Parent.Name
MyRange.Offset(0, 2) = pt.TableRange2.Address
MyRange.Offset(0, 3) = pt.CacheIndex
MyRange.Offset(0, 4) = Application.ConvertFormula _
(pt.PivotCache.SourceData, xlR1C1, xlA1)
MyRange.Offset(0, 5) = pt.PivotCache.RecordCount
'Шаг 6: Переместить курсор строкой вниз и установить якорь
Set MyRange = MyRange.Offset(1, 0)
'Шаг 7: Работа через все сводные таблицы и листы
Next pt
Next ws
'Шаг 8: Соответствие размеров столбцов
ActiveSheet.Cells.EntireColumn.AutoFit
End Sub

Как это код работает

1. Шаг 1 объявляет объект под названием WS. Это создает контейнер памяти для каждого рабочего листа. Затем мы объявляем объект под названием PT, который содержит
   каждый цикл PivotTable . Наконец, мы создаем переменную диапазона под названием MyCell. Эта переменная действует как курсор, когда заполняет список инвентаризации.
2. Шаг 2 создает новый рабочий лист и добавляет заголовки столбцов, которые варьируются от А1 до F1. Обратите внимание, что мы можем добавить заголовки столбцов, используя простой массив, который содержит наши метки заголовка. Этот новый рабочий лист остается активным.
3. Так же, как вы бы вручную помещали курсор в ячейку, если вы должны были начать вводить данные, шаг 3 помещает MyCell курсор в ячейку A2 активного листа. Это наша точка привязки, что позволяет нам перейти отсюда. На протяжении всего макроса, вы используете свойство offset. Свойство offset позволяет передвигать курсор Offset х количество строк и х количество столбцов от точки привязки. Например, Range (A2) .offset (0,1) будет перемещать курсор на один столбец. Если мы хотим, переместить курсор на одну строку вниз, мы вводим Range(A2). Offset(1, 0). В макросе, мы перемещаемся с помощью
   Offset MyCell. Offset (0,4) будет перемещать курсор на четыре столбца справа от анкерной ячейки. После того, как курсор будет на месте, мы можем ввести данные.
4. Шаг 4 начинает зацикливание, говоря Excel, что мы хотим оценить все рабочие листы в этой книге.
5. Шаг 5 перебирает все сводные таблицы в каждом листе. Для каждого PivotTable он находит, извлекает соответствующее свойство и заполняет таблицу на основании положения
   курсора (шаг 3). Мы используем шесть свойств: Name, Parent.Range, TableRange2, CacheIndex, PivotCache.SourceData и PivotCache. RecordCount.
   Свойство Name возвращает имя сводной таблице.Свойство Parent.Range дает нам лист, где постоянно находится PivotTable. Свойство TableRange2.Address возвращает диапазон, где находится PivotTable. Свойство CacheIndex возвращает порядковый номер кэша сводной для сводной таблицы.
   Кэш-память поворота представляет собой контейнер памяти, которая хранит все данные для сводной таблицы. При создании новой сводной таблицы, Excel делает снимок исходных данных и
   создает кэш возврата. Каждый раз при обновлении сводной таблицы, Excel приходит к исходным данным и делает еще один снимок, тем самым обновляя кэш возврата. Каждый кэш имеет свойство SourceData, который идентифицирует местоположение данных, используемых для создания кэш возврата. PivotCache. SourceData свойство сообщает нам, какой диапазон будет призван, когда мы обновить сводную таблицу. Вы также можете вытащить количество записей из исходных данных, используя PivotCache.Recordcount.
6. Каждый раз, когда макрос встречает новую сводную таблицу, он перемещает курсор вниз MyCell строку, начиная новую строку для каждого сводной таблицы.
7. Шаг 7 говорит Excel повторить цикл для всех листов. После того, как все листы были оценены, макрос переходит к последнему шагу.
8. Шаг 8 завершает с небольшим количеством форматирования размеров столбцов, чтобы соответствовало данным.

Как использовать

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

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