CODEWITHSUNDEEP
vbaexcelcharts
GEpi
GEpi

Reputation: 11

Change a excel chart sourcedata with Macro

I am trying to create a macro which will change the sourcedata of a line graph.

The name of the graph is: Tot_Failures, name of the sheet which graph is displayed is: Test_Results, name of the sheet which the graph needs to pull information from is: CycleNumFailures

The code is:

Dim rangeText As String
rangeText = CStr("B1:B" & biggestCycleNum)

Dim chartSheet As Worksheet
Set chartSheet = Sheets("Test_Results")
chartSheet.ChartObjects("Tot_Failures").Activate
ActiveChart.SetSourceData Source:=Sheets("CycleNumFailures").Range(rangeText)

I have tried many variations of line 7 (which is where its failing).

ActiveChart.SetSourceData Source:=Sheets("CycleNumFailures").Range("B1:B" & biggestCycleNum)

and

chartSheet.ChartObjects("Tot_Failures").SetSourceData Source:=Sheets("CycleNumFailures").Range("B1:B" & biggestCycleNum)

the error message is:

run-time error '2147417848 (80010108)': Method 'Range' of object' _worksheet' failed

Please help. Thanks

Upvotes: 1

Views: 1647

Answers (1)

Bryan M
Bryan M

Reputation: 11

instead of

chartSheet.ChartObjects("Tot_Failures").SetSourceData Source:=

try

chartSheet.ChartObjects("Tot_Failures").Chart.SetSourceData Source:=

This worked when I tried it on an example.

Upvotes: 1

Related Questions