Hi, Thank you for any help with following problem:

I want to create dynamic charts, with a similar chart on each
worksheet. The chart values are based on named formulas as range.
This works well as long as I hard code the worksheet name when I use
the named formula as input for the chart values, but not when I want
to use a variable as sheet name.

The example might be clearer:

Sub addstudy_Click()
Dim NewStudyName As String
' ..... (other stuff)

'the program creates a new worksheet and asks the user for a name of
the worksheet, which is assigend to the
' NewStudyName variable

ActiveSheet.Name = NewStudyName
' ...........(other stuff)
'next is the definition of names for the chart source data:
Worksheets(NewStudyName).Names.Add Name:="CDAvalues", RefersToR1C1:= _
"=OFFSET('Worksheets(NewStudyName)'!R40C2,0,1,1,SU M('Worksheets(NewStudyName)'!R38C1))"
'...........(other stuff)
'now I set up the chart:
Dim workchart As Chart
Dim aNewSeries As Series
Set workchart = Charts.Add
ActiveChart.Location Whe=xlLocationAsObject, _
Name:=Worksheets(NewStudyName).Name
With ActiveChart.SeriesCollection(1)
.Name = "CDAs"
.XValues = Worksheets(NewStudyName).Range("c65:cg65")
' alternatively this works too:
.XValues = "=" & "'" & Worksheets(NewStudyName).Name & "'" &
"!R65C3:R65C59"

' and here comes the problem:
'THIS DOESN'T work:
..Values = "=" & "'" & Worksheets(NewStudyName).Name & "'" &
"!CDAvalues"
if I replace the first part of the value assignement by a hard coded
worksheet reference
e.g.:
.Values = "=study1!CDAvalues"
then it works well too.

'rest of the program:
.Name = "=" & "'" & Worksheets(NewStudyName).Name & "'" &
"!R2C2"
End With
' .... (other stuff)

I also tired other versions (and many more that seem less correct):
.Values = Worksheets(NewStudyName).Range("CDAvalues")
.Values = "='Worksheets(NewStudyName)'!CDAvalues"
.Values = "=Worksheets(NewStudyName)!CDAvalues"
.Values = Worksheets(NewStudyName).Names(1).RefersToRange
.Values = Worksheets(NewStudyName).Names("CDAvalues").Refers ToRange
.Values = Names("CDAvalues").RefersToRange

obviously I am lost now.
I want to use worksheet specific names, with workbook names I run into
similar problems (after trying to use formats like:
.Values = "=" & ThisWorkbook.Name & "!CDAvalues" ).

If you could help me I very much appreciate it. I work with EXCEL
2000.

Thank you, Andreas