Sunday, 14 August 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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts