I hope this is the right user group...

I have a sheet of data for a nuber of projects. I then want to;
create a new sheet for each project and generate a chart on the new sheet.
This is all done with VBA.
It works except that I want to use the secondary axis however the code below
crashes excel when it tries to set the axisgroup.

Can anyone tell me where I am going wrong?

Thanks
Graham


Private Sub GeneratePlots()

Dim i, j
Dim shtPlot As Worksheet
Dim s As Worksheet
Dim rx As Range
Dim ry1 As Range
Dim ry2 As Range
Dim ry3 As Range
Dim ra() As Range
Dim oChart As ChartObject
Dim ChtTop As Long, ChtLeft As Long
Dim ChtHeight As Long, ChtWidth As Long


ChtTop = 20
ChtLeft = 20
ChtWidth = 400
ChtHeight = 400

For i = 1 To 2 ' UBound(mArrUIP, 1)

'add new sheet
mcXL.AddAsLastWorksheet CStr(mArrUIP(i, 1))
Set shtPlot = Worksheets(CStr(mArrUIP(i, 1)))

'add new chart to the new (active) sheet
Set oChart = Worksheets(CStr(mArrUIP(i, 1))).ChartObjects.Add(ChtLeft,
ChtTop, ChtWidth, ChtHeight)
oChart.name = CStr(mArrUIP(i, 1)) & "_1"
oChart.Chart.ChartType = xlLine

'add series
ReDim ra(3)
Set rx = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "C"),
mDataSheet.Cells(mArrUIP(i, 3), "C"))
Set ra(1) = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "G"),
mDataSheet.Cells(mArrUIP(i, 3), "G"))
Set ra(2) = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "K"),
mDataSheet.Cells(mArrUIP(i, 3), "K"))
Set ra(3) = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "T"),
mDataSheet.Cells(mArrUIP(i, 3), "T"))

For j = 1 To 3
With oChart.Chart

.SeriesCollection.NewSeries
.SeriesCollection(j).XValues = rx
.SeriesCollection(j).Values = ra(j)

.SeriesCollection(j).MarkerStyle = xlMarkerStyleNone
'.SeriesCollection(j).AxisGroup = xlSecondary <= CRASHES EXCEL

End With
Next

Next i


End Sub

Hi,

It looks like you you are trying to put ALL of the data series on the
secondary axis.
There must be at least 1 series on the primary.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"graham_s" wrote in message
...
I hope this is the right user group...

I have a sheet of data for a nuber of projects. I then want to;
create a new sheet for each project and generate a chart on the new sheet.
This is all done with VBA.
It works except that I want to use the secondary axis however the code
below
crashes excel when it tries to set the axisgroup.

Can anyone tell me where I am going wrong?

Thanks
Graham


Private Sub GeneratePlots()

Dim i, j
Dim shtPlot As Worksheet
Dim s As Worksheet
Dim rx As Range
Dim ry1 As Range
Dim ry2 As Range
Dim ry3 As Range
Dim ra() As Range
Dim oChart As ChartObject
Dim ChtTop As Long, ChtLeft As Long
Dim ChtHeight As Long, ChtWidth As Long


ChtTop = 20
ChtLeft = 20
ChtWidth = 400
ChtHeight = 400

For i = 1 To 2 ' UBound(mArrUIP, 1)

'add new sheet
mcXL.AddAsLastWorksheet CStr(mArrUIP(i, 1))
Set shtPlot = Worksheets(CStr(mArrUIP(i, 1)))

'add new chart to the new (active) sheet
Set oChart = Worksheets(CStr(mArrUIP(i, 1))).ChartObjects.Add(ChtLeft,
ChtTop, ChtWidth, ChtHeight)
oChart.name = CStr(mArrUIP(i, 1)) & "_1"
oChart.Chart.ChartType = xlLine

'add series
ReDim ra(3)
Set rx = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "C"),
mDataSheet.Cells(mArrUIP(i, 3), "C"))
Set ra(1) = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "G"),
mDataSheet.Cells(mArrUIP(i, 3), "G"))
Set ra(2) = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "K"),
mDataSheet.Cells(mArrUIP(i, 3), "K"))
Set ra(3) = mDataSheet.Range(mDataSheet.Cells(mArrUIP(i, 2), "T"),
mDataSheet.Cells(mArrUIP(i, 3), "T"))

For j = 1 To 3
With oChart.Chart

.SeriesCollection.NewSeries
.SeriesCollection(j).XValues = rx
.SeriesCollection(j).Values = ra(j)

.SeriesCollection(j).MarkerStyle = xlMarkerStyleNone
'.SeriesCollection(j).AxisGroup = xlSecondary <= CRASHES EXCEL

End With
Next

Next i


End Sub