PivotTable CurrentPage

[Guest]

I am trying to return the displayed page name of a pivottable. The code that
I have been using is as follows:

Function PvtTablePageName(ByVal pvtTable As PivotTable) As String

Dim pgName As String
Dim pt As PivotTable
Set pt = Worksheets("Sheet1").PivotTables(pvtTable)
pgName = pt.PivotFields("Account #").CurrentPage.Name
PvtTablePageName = pgName

End Function

This returns a #VALUE error. Any help would be appreciated.

Regards,
Mark

 

[Tom Ogilvy]

I assume you are passing in the Pivot Table name as a string.

Function PvtTablePageName(ByVal pvtTable As String) As String
'comment out the next line if this is not used in a worksheet cell
Application.Volatile
Dim pgName As String
Dim pt As PivotTable
Set pt = Worksheets("Sheet1").PivotTables(pvtTable)
pgName = pt.PivotFields("Account #").CurrentPage.Name
PvtTablePageName = pgName

End Function

 

[Guest]

Thank you Tom. Your answer helped me.

Regards,
Mark