Updating charts using named ranges 1

[Stevo911]

hi there

Im trying to update charts in excel using named ranges. To simplify lets say that the x-axis comprises 12 months (jan -> dec) and the y-axis has 2 sets of data, a 'target' series and an 'actual' series.

Lets say that the values for my 'target' data series are found at "=Sheet1!$C$2:$C$10".

Then, in the macro:

ActiveChart.SeriesCollection(1).Values = "=Sheet1!$C$2:$C$10"
ActiveChart.SeriesCollection(1).Name="=""Target"""

This does it. My problem is that i am wanting to use named ranges instead of ["=Sheet1!$C$2:$C$10"].

I have attempted the following:

***

ActiveWorkbook.Names.Add Name:="target1", ReferToR1C1:="=Sheet1!$C$2:$C$10"

ActiveChart.SeriesCollection(1).Values = "" & target1 & ""
ActiveChart.SeriesCollection(1).Name="=""Target"""

***

but this doesnt work. No error message but the values are just not being associated with the graph. Any corrections, ideas or help would be greatly appreciated.

Thank you

 

[SkipVought]

Hi

Try

ActiveChart.SeriesCollection(1).Values = "=Sheet1!Target1"

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Stevo911]

thanks

doesnt work though

 

[SkipVought]

It sure does!

Either the NAME is not valid, the sheet name is not valid, the range of the values is different than the range of the category.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Stevo911]

Thanks Skip

Working now