Macro 64: Create a PivotTable Inventory Summary
When your workbook contains multiple PivotTables, it’s often helpful to have an inventory summary
(similar to the one shown here in Figure 6-1) that outlines basic details about the PivotTables. With
this type of summary, you can quickly see important information like the location of each PivotTable,
the location of each PivotTable’s source data, and the pivot cache index each PivotTable is using.
Figure 6-1: A PivotTable inventory summary.
The following macro outputs such a summary.
How it works
When you create a PivotTable object variable, you expose all of a PivotTable’s properties — prop-
erties like its name, location, cache index, and so on. In this macro, we loop through each PivotTable
in the workbook and extract specific properties into a new worksheet.
Because each PivotTable object is a child of the worksheet it sits in, we have to first loop through
the worksheets in a workbook first, and then loop through the PivotTables in each worksheet.
Take a moment to walk through the steps of this macro in detail.
Sub Macro64()
‘Step 1: Declare your Variables
Dim ws As Worksheet
Dim pt As PivotTable
Dim MyCell As Range
‘Step 2: Add a new sheet with column headers
Worksheets.Add
Range(“A1:F1”) = Array(“Pivot Name”, “Worksheet”, _
“Location”, “Cache Index”, _
“Source Data Location”, _
“Row Count”)
‘Step 3: Start Cursor at Cell A2 setting the anchor here
Set MyCell = ActiveSheet.Range(“A2”)
‘Step 4: Loop through each sheet in workbook
For Each ws In Worksheets
‘Step 5: Loop through each PivotTable
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
‘Step 6: Move Cursor down one row and set a new anchor
Set MyRange = MyRange.Offset(1, 0)
‘Step 7: Work through all PivotTables and
worksheets Next pt
Next ws
‘Step 8: Size columns to fit
ActiveSheet.Cells.EntireColumn.AutoFit
End Sub
1. Step 1 declares an object called ws. This creates a memory container for each worksheet we
loop through. We then declare an object called pt, which holds each PivotTable we loop
through. Finally, we create a range variable called MyCell. This variable acts as our cursor as
we fill in the inventory summary.
2. Step 2 creates a new worksheet and adds column headings that range from A1 to F1. Note
that we can add column headings using a simple array that contains our header labels. This
new worksheet remains our active sheet from here on out.
3. Just as you would manually place your cursor in a cell if you were to start typing data, Step 3
places the MyCell cursor in cell A2 of the active sheet. This is our anchor point, allowing us to
navigate from here.
Throughout the macro, you see the use of the Offset property. The Offset property
allows us to move a cursor x number of rows and x number of columns from an anchor
point. For instance, Range(A2).Offset(0,1) would move the cursor one column
to the right. If we wanted to move the cursor one row down, we would enter Range(A2).
Offset(1, 0).
In the macro, we navigate by using Offset on MyCell. For example, MyCell.
Offset(0,4) would move the cursor four columns to the right of the anchor cell.
After the cursor is in place, we can enter data.
4. Step 4 starts the looping, telling Excel we want to evaluate all worksheets in this workbook.
5. Step 5 loops through all the PivotTables in each worksheet. For each PivotTable it finds, it
extracts out the appropriate property and fills in the table based on the cursor position (see
Step 3).
We are using six PivotTable properties: Name, Parent.Range, TableRange2.
Address, CacheIndex, PivotCache.SourceData, and PivotCache.
Recordcount.
The Name property returns the name of the PivotTable.
The Parent.Range property gives us the sheet where the PivotTable resides. The
TableRange2.Address property returns the range that the PivotTable object sits in.
The CacheIndex property returns the index number of the pivot cache for the PivotTable.
A pivot cache is a memory container that stores all the data for a PivotTable. When you create
a new PivotTable, Excel takes a snapshot of the source data and creates a pivot cache. Each
time you refresh a PivotTable, Excel goes back to the source data and takes another snap-
shot, thereby refreshing the pivot cache. Each pivot cache has a SourceData property that
identifies the location of the data used to create the pivot cache. The PivotCache.
SourceData property tells us which range will be called upon when we refresh the
PivotTable. You can also pull out the record count of the source data by using the
PivotCache.Recordcount property.
6. Each time the macro encounters a new PivotTable, it moves the MyCell cursor down a row,
effectively starting a new row for each PivotTable.
7. Step 7 tells Excel to loop back around to iterate through all PivotTables and all worksheets.
After all PivotTables have been evaluated, we move to the next sheet. After all sheets have
been evaluated, the macro moves to the last step.
8. Step 8 finishes off with a little formatting, sizing the columns to fit the data.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
