Setting Source Data to a Named Range rather than cell Range

[Justin Smith]

In my code I have a handle on my chart, I am trying to set
the source data of the chart to a Named Range in the
current Workbook. Since this occurs within a loop I am
using variable names. Here is a snippit of my code. Any
help at all is appreciated.


'CurrentDataRange computes to an existing Named Range
within the workbook


chrt.SetSourceData Source:=CurrentDataRange,
PlotBy:=xlColumns

 

[Jon Peltier]

Justin -

Basically, that's how you do it. Is CurrentDataRange a VBA range
variable, or is it the name of a defined range in the sheet? I wrote a
little macro, using CurrentDataRange as the VBA range variable and
"MyCurrentDataRange" as the name of the defined range in the worksheet.
The last line of my sub is your line copied and pasted into my code
without any changes:

Sub ChartANamedRange()
Dim chrt As Chart
Dim CurrentDataRange As Range
Set CurrentDataRange = ActiveSheet.Range("MyCurrentDataRange")
Set chrt = ActiveSheet.ChartObjects(1).Chart
chrt.SetSourceData Source:=CurrentDataRange, PlotBy:=xlColumns
End Sub

- Jon

 

[Justin Smith]

Thanks Jon

-----Original Message-----
Justin -

Basically, that's how you do it. Is CurrentDataRange a VBA range
variable, or is it the name of a defined range in the sheet? I wrote a
little macro, using CurrentDataRange as the VBA range variable and
"MyCurrentDataRange" as the name of the defined range in the worksheet.
The last line of my sub is your line copied and pasted into my code
without any changes:

Sub ChartANamedRange()
Dim chrt As Chart
Dim CurrentDataRange As Range
Set CurrentDataRange = ActiveSheet.Range ("MyCurrentDataRange")
Set chrt = ActiveSheet.ChartObjects(1).Chart
chrt.SetSourceData Source:=CurrentDataRange, PlotBy:=xlColumns
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______




.