Sunday, August 14, 2016

VBA Macro to List the Information of all the Pivots of a Workbook into to a New Sheet

How to Get the Information of all the Pivots of a Workbook into to a New Sheet
Sub ListPivotsInfo()
    Dim St As Worksheet
    Dim NewSt As Worksheet
    Dim Pvt As PivotTable
    Dim I, K As Long
    Application.ScreenUpdating = False
    Set NewSt = Worksheets.Add
    NewSt.Name = "Pivot Info"
    I = 1: K = 2
    With NewSt
        .Cells(I, 1) = "Pivot Name"
        .Cells(I, 2) = "Data Source"
        .Cells(I, 3) = "Refreshed By"
        .Cells(I, 4) = "Refreshed On"
        .Cells(I, 5) = "Pivot Sheet"
        .Cells(I, 6) = "Pivot Location"
        For Each St In ActiveWorkbook.Worksheets
            For Each Pvt In St.PivotTables
                I = I + 1
                .Cells(I, 1).Value = Pvt.Name
                .Cells(I, 2).Value = Pvt.SourceData
                .Cells(I, 3).Value = Pvt.RefreshName
                .Cells(I, 4).Value = Pvt.RefreshDate
                .Cells(I, 5).Value = St.Name
                .Cells(I, 6).Value = Pvt.TableRange1.Address
            Next
        Next
        .Activate
    End With
    Application.ScreenUpdating = True
End Sub

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog