How to list all pivot tables from a workbook?
Supposing you have a large workbook which contains multiple pivot tables, now, you want to list all the pivot tables in this workbook, is it possible? Of course, the following VBA code in this article will do you a favor. To know more details, please read the below article.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
The following VBA code can help you to list all the pivot table names along with their attributes, such as source data range, worksheet name, refreshed date and so on.
1. Open your workbook that you want to list all the pivot tables.
2. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module Window.
VBA code: List all pivot tables from a workbook
Sub ListPivotsInfor() 'Update 20141112 Dim St As Worksheet Dim NewSt As Worksheet Dim pt As PivotTable Dim I, K As Long Application.ScreenUpdating = False Set NewSt = Worksheets.Add I = 1: K = 2 With NewSt .Cells(I, 1) = "Name" .Cells(I, 2) = "Source" .Cells(I, 3) = "Refreshed by" .Cells(I, 4) = "Refreshed" .Cells(I, 5) = "Sheet" .Cells(I, 6) = "Location" For Each St In ActiveWorkbook.Worksheets For Each pt In St.PivotTables I = I + 1 .Cells(I, 1).Value = pt.Name .Cells(I, 2).Value = pt.SourceData .Cells(I, 3).Value = pt.RefreshName .Cells(I, 4).Value = pt.RefreshDate .Cells(I, 5).Value = St.Name .Cells(I, 6).Value = pt.TableRange1.Address Next Next .Activate End With Application.ScreenUpdating = True End Sub
4. Then press F5 key to run this code, all the pivot table names, source data range, worksheet name and other attributes are listed in a new worksheet which placed in the front of your active worksheet as following screenshot shown:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agoThis is awesome! I have been looking for a code like this one, but with the addition of showing all active fields per table. I need to clean up the source tables from unnecessary fields (too heavy) and they feed a book of over 300 pivots. I'd prefer not to go one pivot table at a time to figure out which fields I can eliminate... If you could show me how, it would be incredible... Thanks!
To post as a guest, your comment is unpublished.· 1 months agoAmazing!!! thksss!!!!
To post as a guest, your comment is unpublished.· 1 years agoSaved me from a major headache!
Couldn't find what pivot table was causing a "Refresh All Error"
Boom Listed with locations, Thank you so much
To post as a guest, your comment is unpublished.· 1 years agoFor Connection Only info the Data connection info isa property of the PivotCache
I have a workbook that has over 40 pivot tables with a mix of Excel Tables and SQL server Data Connections. I use the following code to keep track of them
Dim wb As Workbook
Dim pvt As PivotTable
Dim wsheet As Worksheet
Dim pc As PivotCache
Set wb = ActiveWorkbook
For Each wsheet In wb.Worksheets
For Each pvt In wsheet.PivotTables
Debug.Print wsheet.Name & ": " & pvt.Name
Set pc = wb.PivotCaches(pvt.CacheIndex)
If pc.SourceType = xlDatabase Then
If pc.QueryType = xlOLEDBQuery Then
To post as a guest, your comment is unpublished.· 1 years agoVery powerful. Thanks so much.