Ask again: display Excel PivotTable Data Source as cellvalue

Question

Sorry I'm a total beginner. 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've treid a few times to replicate the answer provided, but I keep getting #NAME? error for the formula I've used in cell F4: =PivotTableSource("PivotTable1")

I've checked that my PivotTable name is PivotTable1. What else am I doing it wrong?

    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

Answer 1

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: