Need help Utilizing Dynamic ranges for creating charts

[Guest]

Here is my situation. I have to create several charts based on data that may
or may not exist in certain forms. I can program for those variables if I
wasnt using a chart...however my boss wants a chart!!

My specific issue is this:

I can't for the life of me, figure out how to get the values in a chart to
accept anything other than the "Sheet1!R2C1,Sheet1!R2C2" etc etc.

how do I get

"Activechart.SeriesCollection(1).Values = "

to work with dynamic ranges?

 

[Guest]

You need to build the string to pass in something like this...

dim MyAddress as String

With Range("MyDynaRange")
MyAddress = .Parent.Name & "!" & .Address
End With

Activechart.SeriesCollection(1).Values = MyAddress

 

[Toby Erkson]

Here's a snippit of code I'm currently using where an existing chart needs
to by updated but the number of series can vary:
-----
Sheets(sheet_name_goes_here).ChartObjects(1).Activate
'Set chart's data source
With ActiveChart
.ChartArea.Select
.SetSourceData Source:=Range("Chart_Source_Data"), PlotBy:=xlRows
'Point source to named range
End With
-----
My named range, "Chart_Source_Data", is a dynamic range. Here's a sample of
my range:

........Month..................Jan......Feb.....Mar
Page Views.............7693....3651...8443
Visits.........................7443....8338...8878

When the dynamic range is dropped into the chart, the Months are the x-axis
and the numbers are the y-axis with the names (Page Views, Visits) the
series name.