Pivot Table Sourcedata

[sbhat]

Sub PivotID()
Dim wb As Workbook
Dim sh As Worksheet
Dim pt As PivotTable

Set wb = ActiveWorkbook

For Each sh In wb.Worksheets
MsgBox sh.Name & " has " & sh.PivotTables.Count & " pivot tables"
If sh.PivotTables.Count > 0 Then
For Each pt In sh.PivotTables
MsgBox pt.Name & " source data is " & pt.SourceData
Next
End If


Next

End Sub

===========
The above code works in normal course when pivot tables are based on cell-ranges; however, when a pivot table is based on data model the pt.SourceData throws error 1004: object not defined. What is the remedy?

 

[chris smith5]

Hi sbhat,

The 'SourceData' property of a PivotTable object returns the cell range that the PivotTable is based on. However, when a PivotTable is based on a data model, the 'SourceData' property is not applicable and will return an error.

To obtain the source data for a PivotTable that is based on a data model, you can use the 'DataBodyRange' property of the 'ListObject' object that represents the data model table.

Try using this code:

Sub PivotID()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim pt As PivotTable

    Set wb = ActiveWorkbook

    For Each sh In wb.Worksheets
        MsgBox sh.Name & " has " & sh.PivotTables.Count & " pivot tables"
        If sh.PivotTables.Count > 0 Then
            For Each pt In sh.PivotTables
                On Error Resume Next
                MsgBox pt.Name & " source data is " & pt.SourceData
                If Err.Number = 1004 Then
                    Err.Clear
                    MsgBox pt.Name & " source data is " & pt.TableRange1.ListObject.DataBodyRange.Address
                End If
                On Error GoTo 0
            Next pt
        End If
    Next sh
End Sub

This code is for when the PivotTable is based on a single data model table.
Let me know if this works.


Best Regards.

 

[sbhat]

Hi sbhat,

The 'SourceData' property of a PivotTable object returns the cell range that the PivotTable is based on. However, when a PivotTable is based on a data model, the 'SourceData' property is not applicable and will return an error.

To obtain the source data for a PivotTable that is based on a data model, you can use the 'DataBodyRange' property of the 'ListObject' object that represents the data model table.

Try using this code:

Sub PivotID()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim pt As PivotTable

    Set wb = ActiveWorkbook

    For Each sh In wb.Worksheets
        MsgBox sh.Name & " has " & sh.PivotTables.Count & " pivot tables"
        If sh.PivotTables.Count > 0 Then
            For Each pt In sh.PivotTables
                On Error Resume Next
                MsgBox pt.Name & " source data is " & pt.SourceData
                If Err.Number = 1004 Then
                    Err.Clear
                    MsgBox pt.Name & " source data is " & pt.TableRange1.ListObject.DataBodyRange.Address
                End If
                On Error GoTo 0
            Next pt
        End If
    Next sh
End Sub

This code is for when the PivotTable is based on a single data model table.
Let me know if this works.


Best Regards.

Chris: Thank you for addressing my query. I executed the VBA script you provided; as I also had thought and executed yesterday, I used "On Error Resume Next" piece of code to circumvent the error-prone pt.SourceData (when the pivot is based on data model). I got the result without any error. However, the line "

MsgBox pt.Name & " source data is " & pt.TableRange1.ListObject.DataBodyRange.Address is not found executed; otherwise, I still didn't see the data source of those pivots which are based on data model. The pivots based on plain data range (not the data model ones) gave proper source data info.

 

[RoryA]

What information do you actually want returned if it's a data model pivot? Just the words "data model" or something else?

 

[Alex Blakenburg]

If you have multiple pivot's then you could give the code on the contextures site a try, since it will list all the sources on a separate sheet:

Excel Pivot Table Data Source Macros List Info VBA

Excel macro lists all pivot tables with data source location. Excel macro change data source for all pivot tables.

www.contextures.com

The link should take you straight to the section:
List Pivot Table Data Source or MDX

The key part of the code being:

   If pt.PivotCache.OLAP = False Then
      strSource = pt.SourceData
      strMDX = ""
    Else
      strSource = "OLAP"
      strMDX = pt.MDX
    End If

 

[sbhat]

What information do you actually want returned if it's a data model pivot? Just the words "data model" or something else?

The Data Model would be having a "Range" tab which is pointing to a worksheet's cell range in turn. Therefore, source data actually is a worksheet's cell range which is the "Range" for the data model.