Data Source is key factor to create pivot table in excel. On the basis of data source pivot get refreshed and we can made changes in pivot structure. Thus, to know pivot data source is very essential to structure pivot layout. In this topic, we will see how can be data source information for various pivot collected using excel macro.


Here, we go for excel macro which will collect pivot data source details for all pivots in a workbook: 

Option Explicit

Sub Pivot_DataSource()
' Author: Dreams24d
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

'Declare Variables
Dim pvt As PivotTable
Dim ws As Worksheet
Dim strPvt As String
Dim sht As String
Dim pvtSource As String
Dim pvtName As String
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

strPvt = "       Pivot Name     Sheet Name   Source Data" & vbNewLine
i = 1

'For loop through each worksheets
    For Each ws In ActiveWorkbook.Worksheets
        Worksheets(ws.Name).Select

        'Loop to select pivot tables in each worksheet
        For Each pvt In ws.PivotTables
           
           pvtName = pvt.Name
           pvtSource = pvt.SourceData
           sht = ws.Name
           
            'Pass Pivot details to variables in order to show in message. comment or delete this if not required
            strPvt = strPvt & i & ".    " & pvtName & "         " & sht & "       " & pvtSource & vbNewLine
            
            'Uncomment below lines to get pivot table list in sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = pvtName
            'ActiveWorkbook.Sheets("Sheet1").Range("B" & i).Value = sht
            i = i + 1
            
        Next pvt
    Next ws
    
MsgBox "Pivot Tables and their data source listed as Below: " & vbNewLine & vbNewLine & strPvt, vbInformation, "Pivot and Data Source"

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub