how do i get get worksheet name for pivottable sourcedata whenworking on pivotchart

Hello,

I'm working with a pivotchart and like to identify the worksheet or
the worksheet name where the sourcedata is present for the
pivotchart / the pivottable. The code needs to work with named ranges
as sourcedata for the pivottable.

Dim pt As PivotTable
Dim ws as Range

Set pt = ActiveChart.PivotLayout.PivotTable


' handle to the worksheet with the pivottable sourcedata
' Set WS = Worksheets(Left(pt.SourceData, InStr(1, pt.SourceData,
"!", vbTextCompare) - 1)).[a1]
sp = Range(pt.SourceData).Parent.Name

Set WS = Worksheets(sp)