Chart series link to tab name

[likelyladmatty]

Hi there

I have a template tab with charts linked to data within that tab, the data is dynamic to a single cell within the tab and this cell is linked to the tab name. I have copied the whole sheet to over 200 sheets each with their own dynamic tab name. However I have now just realized that the charts are all still linked to the original tab data. I need to be able to make these charts dynamic to the tab they sit in. Is there a way to make 'Sheet1' in the example below dynamic to the tab name for example by using the formula: =RIGHT(CELL("Filename",A1),LEN(CELL("Filename",A1))-FIND("]",CELL("Filename",A1)))

e.g. =SERIES('Sheet1'!$R$8,'Sheet1'!$S$6:$T$7,'Sheet1'!$S$8:$T$8,1)

Any help would be great!

Matty

 

[Zubair Muhammad]

Hi Matty,

You can run this macro. It will change the "Sheet1" in the series formula to the TabNames in which charts are located.
Please make a back up file before running this macro

Sub ChangeChartSeries()
Dim mychart As Chart, i As Integer, j As Integer, k As Integer
For i = 2 To Sheets.Count
    For j = 1 To Sheets(i).ChartObjects.Count
        Set mychart = Sheets(i).ChartObjects(j).Chart
        For k = 1 To mychart.SeriesCollection.Count
            mychart.SeriesCollection(k).Formula = Replace(mychart.SeriesCollection(k).Formula, "Sheet1", Sheets(i).Name)
        Next k
    Next j
Next i
End Sub