Find Range for FullSeriesCollection .Name and .Value

I have a couple of applications where the sheet data are identical in location. What I want to do is "clone" the chart on one of the sheets to the other sheets. If I copy the chart to a new sheet, it still references back to the old location. The "fix" is to activate the chart on the new sheet, go to Select Data and edit each series by changing the name of the sheet from the old sheet to the new sheet.

I would like to automate this process. So I recorded a macro and here's a part of it.

    ActiveChart.FullSeriesCollection(1).Name = "='Page 2'!$A$2"
    ActiveChart.FullSeriesCollection(1).Values = "='Page 2'!$B$2:$D$2"

When I try to reference ActiveChart.FullSeriesCollection(1).Name , I get the contents of cell A2 on page 2. I don't get "='Page 2'!$A$2". I will need to do that so I can write my program.

I can't even reference ActiveChart.FullSeriesCollection(1).Values, but I need to find how to get the "='Page 2'!$B$2:$D$2" part.

So far, everything I've researched tells me how to set these values. In other words, I can tell the chart where to read the data from. I can't tell where it is currently reading the data from.

I finally found what I needed:

ActiveChart.FullSeriesCollection(1).Formula

Yields =SERIES('Page 2'!$A$2,'Page 2'!$B$1:$D$1,'Page 2'!$B$2:$D$2,1)

Which are Series Name, Column Header Names, Series Values. All I have to do is parse them out and I am in business.

I didn't even have to parse. All I had to do was substitute. The original chart was on Page 1. I copied it to another sheet, selected the new sheet and ran the code.

Sub SwitchSource()
Dim MySeries As String              ' Series Formula
Dim MyName
Dim chObj As ChartObject            ' Pointer to chart objects on sheet
Dim NSeries As Long                 ' Number of series
Dim k As Long                       ' Index for series

Dim TSheet As String                ' Template sheet
Dim DSheet As String                ' Destination sheet

' inialize variables
TSheet = "Page 1"                   ' Prompt or otherwise read this value
DSheet = ActiveSheet.Name

' Future enhancement - loop thorough a list of sheets.

' Loop though charts
For Each chObj In ActiveSheet.ChartObjects
    chObj.Activate
    ' Find number of series and loop through them
    NSeries = ActiveChart.FullSeriesCollection.Count
    ' Loop though the series, get the series string and change it
    For k = 1 To NSeries
        MySeries = ActiveChart.FullSeriesCollection(k).Formula
        MySeries = Replace(MySeries, TSheet, DSheet)
        ActiveChart.FullSeriesCollection(k).Formula = MySeries
    Next k
Next chObj

End Sub