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.
List all pivot tables from a workbook with VBA code
List all pivot tables from a workbook with VBA code
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:
Related articles:
How to check if a pivot table exists in a workbook?
How to add multiple fields into pivot table?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!