Hi,

I have about 30 spreadsheet which contains about 50-300 different data series all in columns next to each other. I want to plot them all on top of each other as stacked scatter graphs. I have been trying to write a macro to do this automatically as adding each data series to a chart would take much longer than I can bothered to spend. Was wondering if anyone here could help.

Here is the macro I have attempted so far for a run containing 60 series,

Sub chart()

Dim inc As Integer

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers

For i = 0 To 60
inc = 0
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1 + inc).XValues = Range(Cells(3, 1 + 3 * inc), Cells(466, 1 + 3 * inc))
ActiveChart.SeriesCollection(1 + inc).Values = Range(Cells(3, 3 + 3 * inc), Cells(466, 3 + 3 * inc))
ActiveChart.SeriesCollection(1 + inc).Name = Cells(2, 3 + inc)
inc = inc + 1

Next

ActiveChart.Location Where:=xlLocationAsNewSheet

End Sub


I have attached an example of the data sheet. Each data series is comprised of three columns. The first is labelled 1/d which I want to be my x values, the second is labelled intensity which I do not want in the graph. The third is labeled by a number which I would like to be the title of the data series and the values underneath to be the y values.

Thanks very much for any help!

Sam