Hello Helper

I have an Excel workbook. It contains a chart and then several worksheets.
Each month I add a worksheet with data and I call it e.g. Apr 05 (I place
this on the tab). I then select some data from that worksheet and ass it to
the chart. The chart is updated so it shows all data e.g. Jan 05 Feb 05 Mar
05 Apr 05 and so on.

I wrote a Macro to help me automate the process but I have two main problems
relating to adding a new series to my existing chart:

1) How do I get the name of my new series to be the same as what is
displayed on the tab of my worksheet e.g. Feb 05?

2) How do I get the chart to reference a data series in my worksheet?

I recorded a Macro to see how this might work. For example, I copied the tab
name and pasted it directly into the 'name' field of the chart source data.
Also, I manually selected the data I wanted to display.

The problem is the notation that Excel records this in. For the data series,
it records it using R1C1 notation. I want to use something like...

Sheets("Feb 05"). Range((Cells(x, y), Cells(x, y))

Also, regarding point (1), I got this to work if I refernced a cell inthe
wroksheet that I has set as Feb 05. However, when it was displayed on the
chart it appeared as 1/02/05? How can I get it to display Feb 05 exactly. I
tried setting the cell format to 'Text' but that did not work.

Any help truly appreciated here. My code took 7 hours to write from getting
the data downloaded into Excel and sorting it to pick out the relevant parts.
The final hurdle is the chart part and I never anticipated falling down here!

Please help if you can.

Regards

Alex

Use the Add method of SeriesCollection to add the range:

ActiveChart.SeriesCollection.Add source:=Sheets("Feb 05").[A1:A10]

P Sitaram

Thanks for replying.

Your post put me on the right tack and have now completed my project.

Thanks

Alex

"P Sitaram" wrote:

Use the Add method of SeriesCollection to add the range:

ActiveChart.SeriesCollection.Add source:=Sheets("Feb 05").[A1:A10]