How do I reference charts with the same name, but on different worksheets?

Question

I have two worksheets containing charts, and use a macro to run over all the sheets with charts in them and update the values charted.

However, I run into a problem when attempting to reference the charts in the sheets after the first one - although the reference for the worksheet changes, the reference to the chart does not.

The loop looks like this:

For Each ws In ThisWorkbook.Worksheets
  Debug.Print ws.Name
  Debug.Print ws.ChartObjects("Kortsone").Chart.Name
  With ws.ChartObjects("Kortsone").Chart
    ...
  End With
Next ws

And the output I get to the immediate window is the following:

Grafar ovn 3
Grafar ovn 3 Kortsone
Grafar ovn 4
Grafar ovn 3 Kortsone

As you can see the reference to the worksheet changes, but the chart reference does not.

Is there any way to fix this, or will I have to rename all my charts with unique names?

I'm using Excel 2013

- edit - I have now done some testing based on suggestions in the comments, and it seems that what is printed to the immediate window depends on what is currently the active sheet.

Trying to use for each chartobject ran into the same issues as I had earlier:

Sub test2()
  Dim ws As Worksheet
  Dim ch As ChartObject

  For Each ws In ThisWorkbook.Worksheets
    For Each ch In ws.ChartObjects
      If ws.CodeName = "Graf4" Then
      Debug.Print ws.Name
      Debug.Print ch.Name
      Debug.Print ch.Chart.Name
      End If
    Next ch
  Next ws
End Sub

Gave:

Grafar ovn 4
Kortsone
Grafar ovn 3 Kortsone
Grafar ovn 4
Langsone
Grafar ovn 3 Langsone
...

Answer 1

As you've discovered, the Workheet.ChartObjects method will find the correct ChartObject, but accessing the Chartobject.Chart property will return the Chart of the ActiveSheet. It doesn't matter if you refer to the ChartObject by name or by Index number.

The behavior is the same if you use the Worksheet.Shapes method to find the ChartObject.

This behavior is different to earlier versions of Excel. I confirmed that the code worked in Excel XP/2002, and doesn't work in 2016. I'm unsure when the behavior changed. It might have been 2013, or it might have been a patch to 2013 and 2016? The behavior in Office for mac 2016 is the same (ie. doesn't work)

Until Microsoft comes up with a fix, you'll have to activate the sheet, or activate the ChartObject, before you access the Chart property.

Sub test()
  Dim ws As Worksheet
  Dim co As ChartObject
  For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
    Set co = ws.ChartObjects("Kortsone")

    ws.Activate
    'or
    co.Activate

    Debug.Print co.Chart.Name
    With ws.ChartObjects("Kortsone").Chart
    End With
  Next ws
End Sub

I suggest that you temporarily disable ScreenUpdating, and reactivate the originally activesheet once you're done.