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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

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