using a macro to edit the data range of a chart

[Monkey-See, Monkey-Do]

Does anyone know how to write a macro in order to select the data range of a
chart?

The reason is that I have a macro which inserts a line and then copies data
into that new line from another worksheet. Doing this adjusts the data range
of the chart which already exists in the worksheet and means that the new
line is ignored....

I tried to record a macro to do this but it falls over after clicking select
data.

 

[Joel]

Sub getchartrange()

'get chart name
For Each shp In ActiveSheet.Shapes
If shp.Type = msoChart Then
chrtname = shp.Name
MsgBox ("Chart Name = " & chrtname)
Exit For
End If
Next shp
Set chrt = ActiveSheet.ChartObjects(chrtname)
chrt.Activate
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:B3"), _
PlotBy:=xlRows

End Sub

 

[Jon Peltier]

You don't need a macro to do this. You can use a dynamic defined name for
the X and Y data for each series, and reference these names in the chart
SERIES formula.

Dynamic Charts:
http://peltiertech.com/WordPress/2008/05/14/dynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon