Display Excel PivotTable Data Source as cell value

user_5649584 0
Viewed 64

I want to show the data source of a Pivot Table for transparency.

I did find a related post here: display Excel PivotTable Data Source as cellvalue

I get #NAME? error for the formula I used in cell F4: =PivotTableSource("PivotTable1")

I checked that my PivotTable name is PivotTable1.

Function PivotTableSource(myPivot As String) As String
Dim rawSource As String
Dim a1Source As String
Dim bracket As Long

Application.Volatile
rawSource = ActiveSheet.PivotTables(myPivot).SourceData
a1Source = Application.ConvertFormula(rawSource, xlR1C1, xlA1)
bracket = InStr(1, a1Source, "]")
PivotTableSource = "=" & Mid(a1Source, bracket + 1)
End Function
pivot-table vba excel
1 Answers

The provided snippet appears to be functional for any pivot table on the ActiveSheet that does not have its data added to the Excel Data model as of Excel 2019.

The #Name? error in this case likely indicates that excel cannot find the function that you are trying to call - as Tim Williams pointed out, this is likely caused by having placed the function in the wrong place.

Make sure that you have added the function to a new module under your current workbook in the VBA editor.

You should end up with something that looks along the lines of this:

enter image description here

user_70999060
Related