Update Chart SourceData in dynamic range

[soidog]

Hi all,
I am using Excel 2016 and want to update a "Box and Whisker" chart with new "Source Data" in a dynamic range.
One workaround is to delete "Chart 1" and create a new "Box and Whisker" chart but how to update existing chart?
I have tried below code and others but I can't get it to work.

Sub ChartData1()
Dim ChartRange1 As Range
Dim LR          As Long
LR = Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

    With ActiveSheet.ChartObjects("Chart 1").Chart.ChartArea
        'On Error Resume Next                                                    'With "On Error": "Chart 1" is updated with new "Source" range!
            ActiveChart.SetSourceData Source:=Range(Cells(4, 1), Cells(LR, 7))   'Without "On Error": runtime error 445, object doesn't support this action
        'On Error GoTo 0
    End With
End Sub

Any help is much appreciated.

 

[DanteAmor]

Try this

Sub ChartData1()
  Dim LR          As Long
  LR = Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  With ActiveSheet.ChartObjects("Chart 1").Chart
    .SetSourceData Source:=Range(Cells(4, 1), Cells(LR, 7))
  End With
End Sub

 

[soidog]

Hi and thanks for taking your time with this problem.

Unfortunately I get the same error: runtime error 445, object doesn't support this action
And if I use (in your code) "On Error Resume Next" and "On error GoTO 0" as in my code above, "Chart 1" is updated with new "Source" range!

 

[DanteAmor]

works for me.
Which version of Excel do you have?
How did you create the chart?
You can activate the macro recorder, update the range of the char, stop recorder, copy the generated code and paste it here.

 

[soidog]

Hi,
I have Excel 2016
Now I have created a new worksheet in the same workbook.
Copied "SourceData" to range(A4:G15). (Range(A1:G3) have text).
Manually selected range(A4:G12).
Inserted a new chart in the new sheet. (Insert/Charts/Box and Whisker).
Activated the macro recorder:
I activated the chart by clicking in it and then extended "SourceData" to range(A4:G15).
Stopped recorder.


Recorded code to extend "SourceData":
Sub Macro4()
'
' Macro4 Macro
'


'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SetSourceData Source:=range("Chart!$A$4:$G$15")
End Sub

 

[DanteAmor]

Try this please

Just adjust the name of the chart and the sheet.

Sub ChartData1()
  Dim lr          As Long
  lr = Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  ActiveSheet.ChartObjects("[COLOR=#ff0000]Chart 1[/COLOR]").Activate
  ActiveChart.SetSourceData Source:=Range("[COLOR=#ff0000]Chart[/COLOR]!$A$4:$G$" & lr)
End Sub

 

[soidog]

Hi,


Instead of doing it manually, I used recorded macro from where I started the recording, and still get this error: "runtime error 445, object doesn't support this action". How is that possible?


About your answer #6 :
I think Excel automatically name new charts in workbook with "Chart 1", "Chart 2" etc. etc. hence the "Chart 2" and I had already changed it before I run your code.


Also with your code:
I have tried other sheet name than "Chart".
I have tried with a new workbook, a new worksheet name and Chartname = "Chart 1".


For me, your code makes absolute sense and the code works for you but not me. What am I doing wrong?

 

[DanteAmor]

It is very weird, my version of Excel is 2007. Hopefully if someone with version 2016 can review what is happening.

 

[soidog]

Yes, hopefully someone else can help me with this.
Thanks a lot for your time and effort.

 

[DanteAmor]

Youre welcome.