Dynamic Range Not Updating Chart

[jb_excel_123]

Hi all,

I'm trying to create a set of dynamic charts that will change when I update a dropdown (that has a named range). I've set up the set of charts using a series of named ranges that are broadly formulae that do some sort of dynamic OFFSET to get a range series.

Within the template sheet I can make changes and all the charts will update accordingly, and ahe idea is that I'll be able to replicate this worksheet several times and on each chart just update the dropdown to update all the charts.

The issue I have is that when I copy the worksheet, any charts that are using a named range are retaining the references to the original sheet's dropdown.

I suspect that it's something to do with how the cells are referencing the dropdown-range, but I've tried updating the dropdown-range's scope to be either workbook or the worksheet, and the range in the chart just sticks with whichever range it had when copied.

I'll attempt to show with pictures:

The fund code referenced dictates the data that populates the charts, then the charts are populated using the named ranges:

Then I copy the sheet to a new worksheet, but when I look in the chart I simply get the reference to the previous tab:

The tracking error that I'm referring to is a named range that is referencing the dropdown. In theory this should update the chart with the new data but it doesn't seem to be.

Is there a simple way for this range to update to whichever sheet that it's in, or do I have to manually adjust each of the charts to show the specific worksheet's range?

I'm happy to share more information but didn't want to over provide initially.

Thanks in advance!

 

[NdNoviceHlp]

Hi jb_excel_123 and Welcome to the Board! It seems like if you were to change your named range from the sheet scope to a workbook scope then copying the sheet wouldn't require updating the chart series. However, I've been wrong before. HTH. Dave

 

[NdNoviceHlp]

Whoops. Here's some info on worksheet/workbook named ranges and issues re. copy/cloning sheets. I was wrong as I suspected. Create A Named Range And Choose The Scope
Seems like U may need to VBA an actual change to the chart series by replacing the original sheet name with the new sheet name. Dave

 

[NdNoviceHlp]

Seems like U will have to update the chart series formula with the new sheet name. This code will tease out the original sheet name in the series which will need to be replaced. I don't have any further time to do the replacement part right now, but this should be a start. Dave

Dim SplitA As Variant, SplitB As Variant
Dim Cht As Chart, TempStr As String
Set Cht = ActiveSheet.ChartObjects("Chart 1").Chart
SplitA = Split(Cht.SeriesCollection(1).Formula, ",")
SplitB = Split(Cht.SeriesCollection(1).Formula, "!")
TempStr = Right(SplitB(0), Len(SplitB(0)) - Len(SplitA(0)) - 1)
MsgBox TempStr

 

[NdNoviceHlp]

U can trial this. Please save your file before testing this untested code. Dave
Module code...

Public Function UpdateNamedRange(OldSheetName As String, NewSheetName As String)
Dim SplitA As Variant, SplitB As Variant, NewFormulaStr As String
Dim Cht As Chart, TempStr As String, TempFormulaStr As String
Dim Cnt As Integer, Cnt2 As Integer
For Cnt = 1 To Sheets(OldSheetName).ChartObjects.Count
Set Cht = Sheets(OldSheetName).ChartObjects(Cnt).Chart
For Cnt2 = 1 To Sheets(OldSheetName).ChartObjects(Cnt).Chart.SeriesCollection.Count
SplitA = Split(Cht.SeriesCollection(Cnt2).Formula, ",")
SplitB = Split(Cht.SeriesCollection(Cnt2).Formula, "!")
TempStr = Right(SplitB(0), Len(SplitB(0)) - Len(SplitA(0)) - 1)
TempFormulaStr = Cht.SeriesCollection(Cnt2).Formula
NewFormulaStr = Replace(TempFormulaStr, TempStr, NewSheetName)
Sheets(NewSheetName).ChartObjects(Cnt).Chart.SeriesCollection(Cnt2).Formula = NewFormulaStr
Next Cnt2
Next Cnt
End Function

To operate...

Call UpdateNamedRange("Sheet1", "CopySheet1")

 

[jb_excel_123]

thanks for your tips! I’ll try implementing the function this morning. When I attempted to update using a macro last night I didn’t get very far as I wasn’t sure on the “object” coding for the chart.
I will try the function below to quickly change all of the charts’ references.
Do you know if it makes a difference (in this instance) if the named range’s scope is workbook or worksheet?

 

[jb_excel_123]

U can trial this. Please save your file before testing this untested code. Dave
Module code...

Public Function UpdateNamedRange(OldSheetName As String, NewSheetName As String)
Dim SplitA As Variant, SplitB As Variant, NewFormulaStr As String
Dim Cht As Chart, TempStr As String, TempFormulaStr As String
Dim Cnt As Integer, Cnt2 As Integer
For Cnt = 1 To Sheets(OldSheetName).ChartObjects.Count
Set Cht = Sheets(OldSheetName).ChartObjects(Cnt).Chart
For Cnt2 = 1 To Sheets(OldSheetName).ChartObjects(Cnt).Chart.SeriesCollection.Count
SplitA = Split(Cht.SeriesCollection(Cnt2).Formula, ",")
SplitB = Split(Cht.SeriesCollection(Cnt2).Formula, "!")
TempStr = Right(SplitB(0), Len(SplitB(0)) - Len(SplitA(0)) - 1)
TempFormulaStr = Cht.SeriesCollection(Cnt2).Formula
NewFormulaStr = Replace(TempFormulaStr, TempStr, NewSheetName)
Sheets(NewSheetName).ChartObjects(Cnt).Chart.SeriesCollection(Cnt2).Formula = NewFormulaStr
Next Cnt2
Next Cnt
End Function

To operate...

Call UpdateNamedRange("Sheet1", "CopySheet1")

Thanks for this suggestion NdNoviceHlp - it's a fantastic formula that is doing 99% of what I need it to do!

I went through each chart and renamed them Charts 1 to # so that when the macro counted the charts it would refer to the correct one, however I believe there is a cache that still refers to their original names.

So what is happening in some instances is I'm getting "Invalid procedure call or argument" at "TempStr = Right(SplitB(0), Len(SplitB(0)) - Len(SplitA(0)) - 1)" because (I think) the macro is referencing a previous Chart number and series count, and trying to update that i.e. the chart it should be referring to has 2 series and while it's updating that one, it's referring to the count of the series of a different chart on screen i.e. the code is updating all of the points on chart 1 and that has 2 series', but the legacy chart 1 has 10 series so it's trying to update series 3-10 that don't exist. This I've fixed with a simple On Error Resume Next, which gets around that with no issues.

There is another chart that it dislikes even more though, and this one doesn't seem to budge. The error I get is the classic: "Application-defined or object-defined error" on the line "Sheets(NewSheetName).ChartObjects(Cnt).Chart.SeriesCollection(Cnt2).Formula = NewFormulaStr" and this is the one that has about 10 different series on it so it'd be good to get this one working! In theory the formula should be working as the watch list shows me what it should look like, but it doesn't seem to be:

It seems that an object needs to be defined somewhere, but I don't see why this would differ to the others. The chart type is an area, while the others consist of either lines, columns or both. I've tried switching it to a line chart and that doesn't fix the problem (I was hoping for an easy win!). Any thoughts?

Note I've not edited your code apart from adding a sub that calls the function.

 

[NdNoviceHlp]

I'm not sure what's going on. I'm unable to replicate the sheet copy without XL updating the series with the new sheet name replacing the original sheet name? How are U copying the sheet? You didn't need to rename your charts. The code uses XL's internal numerical storage number to refer to the charts. You can re-trial this code. It should just replace the existing sheet name in each series with the new sheet name passed to it. HTH. Dave

Public Function UpdateNamedRange(NewSheetName As String)
Dim SplitA As Variant, SplitB As Variant, NewFormulaStr As String
Dim Cht As Chart, TempStr As String, TempFormulaStr As String
Dim Cnt As Integer, Cnt2 As Integer
For Cnt = 1 To Sheets(NewSheetName).ChartObjects.Count
Set Cht = Sheets(NewSheetName).ChartObjects(Cnt).Chart
For Cnt2 = 1 To Sheets(NewSheetName).ChartObjects(Cnt).Chart.SeriesCollection.Count
SplitA = Split(Cht.SeriesCollection(Cnt2).Formula, ",")
SplitB = Split(Cht.SeriesCollection(Cnt2).Formula, "!")
TempStr = Right(SplitB(0), Len(SplitB(0)) - Len(SplitA(0)) - 1)
TempFormulaStr = Cht.SeriesCollection(Cnt2).Formula
NewFormulaStr = Replace(TempFormulaStr, TempStr, "'" & NewSheetName & "'")
Sheets(NewSheetName).ChartObjects(Cnt).Chart.SeriesCollection(Cnt2).Formula = NewFormulaStr
Next Cnt2
Next Cnt
End Function

To operate eg...

Call UpdateNamedRange("Sheet1 (2)")

 

[jb_excel_123]

I'm not sure what's going on. I'm unable to replicate the sheet copy without XL updating the series with the new sheet name replacing the original sheet name? How are U copying the sheet? You didn't need to rename your charts. The code uses XL's internal numerical storage number to refer to the charts. You can re-trial this code. It should just replace the existing sheet name in each series with the new sheet name passed to it. HTH. Dave

Public Function UpdateNamedRange(NewSheetName As String)
Dim SplitA As Variant, SplitB As Variant, NewFormulaStr As String
Dim Cht As Chart, TempStr As String, TempFormulaStr As String
Dim Cnt As Integer, Cnt2 As Integer
For Cnt = 1 To Sheets(NewSheetName).ChartObjects.Count
Set Cht = Sheets(NewSheetName).ChartObjects(Cnt).Chart
For Cnt2 = 1 To Sheets(NewSheetName).ChartObjects(Cnt).Chart.SeriesCollection.Count
SplitA = Split(Cht.SeriesCollection(Cnt2).Formula, ",")
SplitB = Split(Cht.SeriesCollection(Cnt2).Formula, "!")
TempStr = Right(SplitB(0), Len(SplitB(0)) - Len(SplitA(0)) - 1)
TempFormulaStr = Cht.SeriesCollection(Cnt2).Formula
NewFormulaStr = Replace(TempFormulaStr, TempStr, "'" & NewSheetName & "'")
Sheets(NewSheetName).ChartObjects(Cnt).Chart.SeriesCollection(Cnt2).Formula = NewFormulaStr
Next Cnt2
Next Cnt
End Function

To operate eg...

Call UpdateNamedRange("Sheet1 (2)")

Hi Dave. Thanks for your help here. It's still causing the same issue as before and I'm not sure why. I'm copying the sheets by using a simple macro:

Sub CopyToNewWorksheet()

Dim rFundCode As Range, sNewName As String

Set rFundCode = Range("rFundCode"): sNewName = rFundCode.Value
ActiveSheet.Copy Before:=Sheets(1)
ActiveSheet.Name = sNewName

End Sub

When I step through the code I can see it selecting the different series within the chart but it errors out on each, which is why I have an On Error Resume Next in place now. In order to proceed I've manually updated the single chart that hasn't worked, and am good to go now. Given that the initial scope was to understand why they were not updating, I can mark this sub as complete, noting that I was able to do this 99% using macros thanks to your help

 

[NdNoviceHlp]

I still can't replicate your scenario using your sheet copy code? I'm not sure why U continue to get errors with the above code? My testing shows that the sheet name for all series and all charts are changed using that code. I'm guessing I'm of no further assistance. You are welcome. Thanks for posting your outcome. Dave