Setting Chart Data Source Range Offsetted from ChartObject

JJC129

New Member
Joined
Mar 13, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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))
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 13, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 13, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 13, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 13, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

JJC129

New Member
Joined
Mar 13, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,688
Members
416,935
Latest member
Atulcp

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