Dynamic Name Range in chart code

[lcfc2kotmail]

Hi, I have a macro which contains

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=?????

ActiveChart.SetSourceData
Range("R16").Select
End Sub

I have added the ??? as I am unsure how do I call in that part, the name of my named ranged which I have called chartdata.

Reason I need this is the name data contains a formula to make my range dynamic for a chart range...

=OFFSET('Sheets3'!$A$1,0,0,COUNTA('Sheet3'!$A:$A),COUNTA('Sheet3'!$1:$1)).

Or does anybody no how I can incorporate that into creating my chart as it will be dynamic based on the data on that sheet.

Many Thanks!!

[Bob Phillips]

With ActiveSheet.ChartObjects(1)
.Chart.ChartType = xlLine
.Chart.SetSourceData Source:=ActiveSheet.Range("chartdata")
End With

[lcfc2kotmail]

Hi,

this looks fine however getting errors when I run it,

basically the namedrange refers to data on sheet 3 and I want my chart to appear on sheet4, how do i go about changing this, and also have the chart resize automaticall depending on data points.

Im fairly new at trying to use graph objects within vba

[Bob Phillips]

You won't be able to set the whole range to a named range, Excel automatically converts it to the cell references.

To achieve that, you need to name each data series, and set each one individually.