Hello,

I am creating a chart manually but I would like to use macro to change the source data. To be honest, I have done loads of research before giving up myself and try whether someone could kindly help. I worked out how to select the data if a cell is known but I got stuck when I range is specified by a variable. Here's is my code:

Sheets("Chart").ChartObjects("chart1").Activate

ActiveChart.SeriesCollection(1).Values = "='Data'!$a$2:$a$36"
So a few questions:
1.) how to you change 'Data'!$a$2:$a$36" to a variable?

2.) None of my data source in my chart is called "SeriesCollection(1)", how does it define as SeriesCollection number "1"

3.) If I need to change the data "SeriesCollection(1)" to a secondary axis, which code should I use?

4.) I tried to put Sheets("Chart").ChartObjects("chart1").SeriesCollection(1).Values = "='Data'!$a$2:$a$36, how come it does not work?

Sorry for throwing out loads of questions But I will be very VERY grateful if anyone can help or even the time spent to read this.

Many thanks!!

Gordon