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))

 

[mart37]

The last line select a range above the chart. The code is correct. The mistake is, I think, in the data in the range.
You can control the range with:
Debug.Print Range(TopLeftCell.Offset(-4, 0), TopLeftCell.Offset(-1, 6)).Address

 

[JJC129]

Thanks for the quick reply! I put your line in under "ActiveSheet.ChartObjects(1).Activate" and took off my set source data line. Now I'm getting the same error message with your line! Any ideas?

 

[JJC129]

The last line select a range above the chart. The code is correct. The mistake is, I think, in the data in the range.
You can control the range with:
Debug.Print Range(TopLeftCell.Offset(-4, 0), TopLeftCell.Offset(-1, 6)).Address

Thanks for the quick reply! I put your line in under "ActiveSheet.ChartObjects(1).Activate" and took off my set source data line. Now I'm getting the same error message with your line! Any ideas?

 

[mart37]

When the topleftcell is in row 1-3 you get an error. Offset(-4, 0) is then not possible! (3 - 4 = -1 does not exist)

 

[JJC129]

When the topleftcell is in row 1-3 you get an error. Offset(-4, 0) is then not possible! (3 - 4 = -1 does not exist)

Thank you, haha silly mistake on my end.

 

[JJC129]

When the topleftcell is in row 1-3 you get an error. Offset(-4, 0) is then not possible! (3 - 4 = -1 does not exist)

Sorry to bother you once more, but I'm trying now to apply this macro to all the ChartObjects in the worksheet. What I have is below:

Why does Excel keep saying that method or datamember is not found? For the TopLeftCell line.

Dim TopLeftCell As Range

Worksheets("Sheet2").Activate

Dim ws As Worksheet
Dim chtO As ChartObject
Set ws = Worksheets("Sheet2")

For Each chtO In ws.ChartObjects

With chtO.Chart

.TopLeftCell.Select

Set TopLeftCell = ActiveCell

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

End With
Next chtO

End Sub

 

[mart37]

With chtO

 

[JJC129]

With chtO

Thanks again, but now I get a problem with the SetSourceData (Sub or function not defined) line. I looked it up and it said possible problems were misspellings or references but the spelling is good and the references should be mentioned already with TopLeft Cell. Ideas?

For Each chtO In ws.ChartObjects

TopLeftCell.Select

Set TopLeftCell = ActiveCell

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

Next chtO

End Sub

 

[mart37]

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