Excel Chart Source Data Sheet Name

[inkserious]

I have a workbook that contains six or seven sheets. I have a Pivot Chart on Sheet5 whose source data comes from a Pivot Table on Sheet2. I've created a button on the Pivot Chart sheet that when clicked will hide the sheet. Once clicked, I would like the Worksheet that the Pivot Chart source data is coming from to be selected.

So if the button on Sheet5 is clicked, Sheet2 will be selected. I have an example below but this requires me to create a separate Sub for each chart as it hard codes the sheet that needs to be selected. How can I extract the Pivot Chart's source data Sheet name and pass it to a variable and then use that to select the sheet?

Any help would be greatly appreciated.

-ep

Sub HideChart()

Dim ws As Worksheet
Set ws = Sheets("Sheet2")

ActiveSheet.Visible = xlSheetHidden
ws.Select

End Sub

 

[1726]

Try a statement of the form:

strVariable = PivotTableReference.Parent.Name

 

[SkipVought]

VBA questions are best handled in forum707.

You might have to do something like this...

dim a
a = Split(ChartObjects(1).Chart.SeriesCollection(1).Formula, "(")
SheetName = split(a(1), "!")(0)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[inkserious]

Thanks guys. This is what I came up with. Let me know if you see anywhere I can improve.

-edward

Sub HideChart()

Dim SheetName As String
Dim ws As Worksheet

    SheetName = Split(ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Formula, "(")(1)
    SheetName = Split(SheetName, "!")(0)
    SheetName = Left(SheetName, Len(SheetName) - 1)
    SheetName = Right(SheetName, Len(SheetName) - 1)
    
Set ws = Sheets(SheetName)

ActiveSheet.Visible = xlSheetHidden
ws.Select

End Sub