using a macro to edit the data range of a chart
 

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.

using a macro to edit the data range of a chart
 

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

"Monkey-See, Monkey-Do" wrote:

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.

using a macro to edit the data range of a chart
 

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/200...ynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Joel" wrote in message
...
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

"Monkey-See, Monkey-Do" wrote:

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.