VBA – Excel – Simple PivotTable Report

Simple PivotTable report to show basic information about PivotTables in the activework. Useful in tracking down lost PivotTables and other similar tasks.

[code lang=”vb”]
Sub ReportOnPTs()

‘Quick report on pivottable details for current workbook

Dim CWB As Workbook
Dim CWS As Worksheet
Dim CPT As PivotTable
Dim CPC As PivotCache
Dim PivCount As Integer, CacheCount As Integer
Dim PivNum As Integer, CacheNum As Integer
Dim RepStr As String, PivListStr As String

Set CWB = ActiveWorkbook
For Each CWS In CWB.Worksheets

PivCount = PivCount + CWS.PivotTables.Count
For Each CPT In CWS.PivotTables

PivNum = PivNum + 1
PivListStr = PivListStr & Chr(10) & PivNum & ") PT Name: " & _
CPT.Name & " – Cache Index: " & CPT.PivotCache.Index & _
" – Rng: " & CPT.SourceData & _
" – WS Name: " & CPT.Parent.Name

Next CPT
Next CWS

RepStr = _
"PivotTable count: " & PivCount & Chr(10) & _
"PivotCache count: " & CWB.PivotCaches.Count & Chr(10) & _
PivListStr

‘Select with to output report to the immediate window or a message box here…
‘MsgBox RepStr
Debug.Print RepStr

End Sub

[/code]

Example output:

Pivot table count: 3
Pivot Cache count: 3

1) PT Name: SalesPT - Cache Index: 3 - Rng: 'Data Sheet'!R1C1:R12597C33 - WS Name: Table
2) PT Name: IndivCustPT - Cache Index: 2 - Rng: Sheet5!R1C1:R3846C42 - WS Name: Indiv Cust. Rep
3) PT Name: TmpWorkingPT - Cache Index: 1 - Rng: 'Data Sheet'!R1C1:R12225C27 - WS Name: Report Working Sheet