Setting Chart Data Source Range Offsetted from ChartObject

[JJC129]

Good day everyone,

Looking to get your advice on how to move forward. I'm trying to update a chart's source data after being copied to a new worksheet using the macro below. I would like to set the new data source using an offset from the chartobject itself. Here's what I have below:
Not sure why the last line keeps giving me: Application-defined or object-defined error. Any ideas?



Dim TopLeftCell As Range



Worksheets("Sheet2").Activate

ActiveSheet.ChartObjects(1).Activate

ActiveSheet.ChartObjects(1).TopLeftCell.Select

Set TopLeftCell = ActiveCell

ActiveSheet.ChartObjects(1).Activate

ActiveChart.SetSourceData source:=Range(TopLeftCell.Offset(-4, 0), TopLeftCell.Offset(-1, 6))

 

[JJC129]

For Each chtO In ws.ChartObjects
  With chtO
     .TopLeftCell.Select
     Set TopLeftCell = ActiveCell
     .SetSourceData Source:=Range(TopLeftCell.Offset(0, -3), TopLeftCell.Offset(6, -1))
  End With
Next chtO

I'm getting an object doesn't support this property or method error. Sorry this is so problematic, I'm trying to understand on my end as well.

 

[JJC129]

I'm getting an object doesn't support this property or method error. Sorry this is so problematic, I'm trying to understand on my end as well.

This is at the SetSourceData line.

 

[mart37]

.Chart.SetSourceData Source:=Range(TopLeftCell.Offset(0, -3), TopLeftCell.Offset(6, -1))

 

[JJC129]

.Chart.SetSourceData Source:=Range(TopLeftCell.Offset(0, -3), TopLeftCell.Offset(6, -1))

Wow. Worked like a charm. I hope to be as proficient as you one day Thank you!