How to use name range in VBA code for a new chart series' values and X-Values?

Question

I am enhancing a work template that requires users to create a new file each week to be able to track progress from week to week, so the file will change name each week.

This file has about 3 charts in a "Summary" worksheet.

In this "Summary" worksheet, bar chart #2 currently has 5 series. I am writing a script to be able to add a 6th series.... this I have no problem.

I would like the new Series values to use the values from a Name Range of cells the template currently has defined ("software") in a different worksheet.

I would also like the Horizontal Axis Labels to use a different Name Range of cells that template also has defined ("dates") in a different worksheet.

Here is the code I have so far:

Sub add_software()
'Update software Trend in Summary

Sheets("Summary").SelectActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection.NewSeriesActiveChart.FullSeriesCollection(6).Name = "='Summary'!$C$44"

'MY PROBLEM IS HERE

ActiveChart.FullSeriesCollection(6).Value = ActiveWorkbook.Sheets("Summary").Range("Software")
ActiveChart.FullSeriesCollection(6).XValues = ActiveWorkbook.Sheets("Summary").Range("dates")

End sub

I would expect that the script will use the values in these Name Ranges (which are the same values used in the other 5 series) and populate with the values from the name range.

This is the error I get is:

Run-Time error '438':
Object doesn't support this property or method error

Answer 1

Try this other method to get the range. Not tested but it should work.

ActiveChart.FullSeriesCollection(6).Value = ThisWorkbook.Names("Software").RefersToRange
ActiveChart.FullSeriesCollection(6).XValues = ThisWorkbook.Names("dates").RefersToRange

Edit: Had an extra dot after "Names".

Answer 2

The accepted solution by @RicardoA works fine, but it assumes a static definition of the names, and places the current address of each name into the SERIES formula of the series. If the names are dynamic, the SERIES formula will not keep up with the changes. The following puts the names not addresses of the names into the series formula:

ActiveChart.SeriesCollection(6).XValues = "='" & ThisWorkbook.Name & "'!dates"
ActiveChart.SeriesCollection(6).Values = "='" & ThisWorkbook.Name & "'!software"

Answer 3

u have a typo. u have miss a "

where u have

.Range("Software)

replace to

.Range("Software")

good luck