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
objNewSheet.Activate

iRow = 2
iSht = 2

'SET TITLES
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"

'GET PIVOT DETAILS
Do While iSht <= Worksheets.Count
Sheets(iSht).Select

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
Next

iSht = iSht + 1
Loop

objNewSheet.Activate

Application.ScreenUpdating = True

End Sub


Back to Resources

Share:

Get in touch

Give our team a call to have a chat through the support that you need.

01225 350730

Email

What our clients say

 


Our Clients