Hello,

I have a template worksheet that I use to create other worksheets by copying and pasting sections of the template on to new worksheets. The template contains two charts. The charts that are copied onto the new worksheet are still referencing the template to get its series ranges from but I need them to be referencing the new worksheet.

Since the name of the new worksheet is everchanging, how do I reference it in the series values? I was thinking I can use the macro to edit like this...

ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.SeriesCollection(1).XValues = "='Sheet2'!$B$12:$B$21"
    ActiveChart.SeriesCollection(1).Values = "='Sheet2'!$H$12:$H$21"
And this will edit the charts data to look like this:

Capture.PNG

But I don't know the worksheet name (eg. Sheet2 could be a large number of things) so I am not sure what to put in my code that will insert the active worksheets name here...

ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.SeriesCollection(1).XValues = "='?????????'!$B$12:$B$21"
    ActiveChart.SeriesCollection(1).Values = "='?????????'!$H$12:$H$21"
[/CODE]


Hope that makes sense, perhaps it is not possible?