We use cookies to improve our site and your experience. By continuing to browse on this website you accept the use of cookies. Find out more about our cookes policy.

How To Excel
VBA to create a list of Pivot Tables in Excel

The macro below will analyse your Excel workbook and create a list of pivot tables and their attributes in a new sheet. This is a great tool for auditing complex Excel spreadsheets containing multiple pivot tables.

Sub list_Pivots()

Dim pvt As PivotTable
Dim iSht As Long
Dim iRow As Integer

Application.ScreenUpdating = False
Set objNewSheet = Worksheets.Add

iRow = 2
iSht = 2

Range("A1").FormulaR1C1 = "Name"
Range("B1").FormulaR1C1 = "Source"
Range("C1").FormulaR1C1 = "Refreshed by"
Range("D1").FormulaR1C1 = "Refreshed"
Range("E1").FormulaR1C1 = "Sheet"
Range("F1").FormulaR1C1 = "Location"

Do While iSht <= Worksheets.Count

For Each pvt In ActiveSheet.PivotTables
objNewSheet.Cells(iRow, 1).Value = pvt.Name
objNewSheet.Cells(iRow, 2).Value = pvt.SourceData
objNewSheet.Cells(iRow, 3).Value = pvt.RefreshName
objNewSheet.Cells(iRow, 4).Value = pvt.RefreshDate
objNewSheet.Cells(iRow, 5).Value = ActiveSheet.Name
objNewSheet.Cells(iRow, 6).Value = pvt.TableRange1.Address
iRow = iRow + 1

iSht = iSht + 1


Application.ScreenUpdating = True

End Sub


Thoughts or questions inspired by this article? Call or email to share them with us.

Call +44 1225 350730

Email us