List All Pivot Tables and their Sources for a given workbook into sheet.
Thanks to https://www.vbausefulcodes.com/vbausefulcodes/61/list-all-pivot-tables-and-it-sources
Sub ListPivotsInfo(IntoSheet, Optional CellA1 = "A1", Wb = "This")
' List All Pivot Tables and their Sources for a given workbook into sheet
' Uses 6 columns starting from CellA1
' The following code will loop through all pivot tables in workbook and print Name, Source, Refreshed By, Refreshed on, Sheet name, location of pivot tables.
' https://www.vbausefulcodes.com/vbausefulcodes/61/list-all-pivot-tables-and-it-sources
' ##NOT Tested## '
Dim St As Worksheet
Dim pt As PivotTable
Dim I As Long
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
Application.ScreenUpdating = False
With Workbooks(WB).Worksheets(IntoSheet)
.Range(CellA1).Currentregion.Entirecolumn.clearcontents
.Range(CellA1).offset(, 0).value = "Name"
.Range(CellA1).offset(, 1).value = "Source"
.Range(CellA1).offset(, 2).value = "Refreshed by"
.Range(CellA1).offset(, 3).value = "Refreshed"
.Range(CellA1).offset(, 4).value = "Sheet"
.Range(CellA1).Offset(, 5).value = "Location"
I = 0
For Each St In Workbooks(WB).Worksheets
For Each pt In St.PivotTables
I = I + 1
.Range(CellA1).Offset(I, 0).value = pt.Name
.Range(CellA1).Offset(I, 1).Value = pt.SourceData
.Range(CellA1).Offset(I, 2).Value = pt.RefreshName
.Range(CellA1).Offset(I, 3).Value = pt.RefreshDate
.Range(CellA1).Offset(I, 4).Value = St.Name
.Range(CellA1).Offset(I, 5).Value = pt.TableRange1.Address
Next
Next
' .Activate
End With
Set St = Nothing
Set pt = Nothing
Application.ScreenUpdating = True
End Sub
' List All Pivot Tables and their Sources for a given workbook into sheet
' Uses 6 columns starting from CellA1
' The following code will loop through all pivot tables in workbook and print Name, Source, Refreshed By, Refreshed on, Sheet name, location of pivot tables.
' https://www.vbausefulcodes.com/vbausefulcodes/61/list-all-pivot-tables-and-it-sources
' ##NOT Tested## '
Dim St As Worksheet
Dim pt As PivotTable
Dim I As Long
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
Application.ScreenUpdating = False
With Workbooks(WB).Worksheets(IntoSheet)
.Range(CellA1).Currentregion.Entirecolumn.clearcontents
.Range(CellA1).offset(, 0).value = "Name"
.Range(CellA1).offset(, 1).value = "Source"
.Range(CellA1).offset(, 2).value = "Refreshed by"
.Range(CellA1).offset(, 3).value = "Refreshed"
.Range(CellA1).offset(, 4).value = "Sheet"
.Range(CellA1).Offset(, 5).value = "Location"
I = 0
For Each St In Workbooks(WB).Worksheets
For Each pt In St.PivotTables
I = I + 1
.Range(CellA1).Offset(I, 0).value = pt.Name
.Range(CellA1).Offset(I, 1).Value = pt.SourceData
.Range(CellA1).Offset(I, 2).Value = pt.RefreshName
.Range(CellA1).Offset(I, 3).Value = pt.RefreshDate
.Range(CellA1).Offset(I, 4).Value = St.Name
.Range(CellA1).Offset(I, 5).Value = pt.TableRange1.Address
Next
Next
' .Activate
End With
Set St = Nothing
Set pt = Nothing
Application.ScreenUpdating = True
End Sub
IntoSheet, Optional CellA1 = "A1", Wb = "This"
Views 124
Downloads 53
CodeID
DB ID
ANmarAmdeen
613
Revisions
v1.0
Sunday
August
13
2023