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.

				
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		

	

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