Dynamic Range Not Updating Chart

jb_excel_123

New Member
Joined
Nov 18, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:
1637248780568.png


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:

1637248981766.png


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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,957
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,957
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
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,957

ADVERTISEMENT

U can trial this. Please save your file before testing this untested code. Dave
Module code...
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...
Code:
Call UpdateNamedRange("Sheet1", "CopySheet1")
 

jb_excel_123

New Member
Joined
Nov 18, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 18, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

U can trial this. Please save your file before testing this untested code. Dave
Module code...
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...
Code:
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:

1637320950335.png


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

Well-known Member
Joined
Nov 9, 2002
Messages
2,957
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
Code:
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...
Code:
Call UpdateNamedRange("Sheet1 (2)")
 

jb_excel_123

New Member
Joined
Nov 18, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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...
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,957
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,127
Messages
5,768,288
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top