How to find the source data of a PivotTable using VBA?

To find the source data of a PivotTable using VBA, you can access the `PivotTable` object model to identify its data source. Below is a simple VBA script that shows how to retrieve the source data range or connection for a PivotTable in Excel.

Sub FindPivotTableSourceData()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim dataSource As String
    
    ' Change the sheet and pivot table name as necessary
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Adjust to your sheet name
    Set pt = ws.PivotTables("PivotTable1")     ' Adjust to your pivot table name
    
    ' Check if the PivotTable is based on a Table Range
    On Error Resume Next
    dataSource = pt.SourceData
    On Error GoTo 0
    
    If dataSource <> "" Then
        MsgBox "The PivotTable is based on the following range or table: " & vbCrLf & dataSource
    Else
        ' If the PivotTable is based on a different type of connection like an external source
        If pt.PivotCache.SourceData <> "" Then
            MsgBox "The PivotTable is based on a connection: " & pt.PivotCache.Connection
        Else
            MsgBox "Unable to determine the source of the PivotTable."
        End If
    End If
End Sub

Open the VBA Editor:

Press `ALT` + `F11` to open the VBA editor.

Insert a Module:

In the VBA editor, right-click on any of the objects for your workbook.
Select `Insert` > `Module` to add a new module.

Paste the following VBA code:
Run the VBA Script:

Make sure you replace `”Sheet1″` with the name of your worksheet and `”PivotTable1″` with the name of your pivot table.
Press `F5` or use the ‘Run’ button to execute the script.

Explanation:

The script first tries to retrieve the source data range using `pt.SourceData`, which works if the PivotTable is based on a worksheet range or table.
If not available, it tries to access `pt.PivotCache.Connection`, which applies when the PivotTable is connected to an external data source or a data model.
The script then shows a message box with the location of the data source, whether it is a range inside the workbook or an external connection.

This approach covers both Excel-based ranges and connections to external data sources, helping you locate the origin of your PivotTable data. Adjust the script for your specific workbook by changing the worksheet and pivot table names.

facebooklinkedin