ListPivotsInfo

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