TimboTim
01-07-2013, 02:53 AM
Hey guys and girls,
I'm using the below code to generate a chart however this chart seems to generate a extra series. Here is what shows in the formula when selecting the series:
=SERIES(Menu!$B$5,Menu!$A$6:$A$17,Menu!$B$6:$B$17,1)
Here is my code I have created and using:
With Charts.Add ' Make new chart
' Set its properties
.ChartType = xlLine
.Location Where:=xlLocationAsNewSheet, Name:="C" & Right(ws3a, 4)
.Move After:=Sheets(ws3a)
With .SeriesCollection.NewSeries
.XValues = "(" & ws3a & "!$D$3," & ws3a & "!$J$3," & ws3a & "!$P$3," & ws3a & "!$V$3," & ws3a & "!$AB$3," & ws3a & "!$AH$3," & ws3a & "!$AN$3," & ws3a & "!$AT$3," & ws3a & "!$AZ$3," & ws3a & "!$BF$3," & ws3a & "!$BL$3," & ws3a & "!$BR$3)"
.Values = "(" & ws3a & "!$F$189," & ws3a & "!$L$189," & ws3a & "!$R$189," & ws3a & "!$X$189," & ws3a & "!$AD$189," & ws3a & "!$AJ$189," & ws3a & "!$AP$189," & ws3a & "!$AU$189," & ws3a & "!$AU$189," & ws3a & "!$AV$189," & ws3a & "!$BA$189," & ws3a & "!$BA$189," & ws3a & "!$BB$189," & ws3a & "!$BH$189," & ws3a & "!$BN$189," & ws3a & "!$BT$189)"
.Name = "Total Income"
End With
With .SeriesCollection.NewSeries
.Values = "(" & ws3a & "!$G$189," & ws3a & "!$M$189," & ws3a & "!$S$189," & ws3a & "!$Y$189," & ws3a & "!$AE$189," & ws3a & "!$AK$189," & ws3a & "!$AQ$189," & ws3a & "!$AW$189," & ws3a & "!$BB$189," & ws3a & "!$BH$189," & ws3a & "!$BB$189," & ws3a & "!$BC$189," & ws3a & "!$BH$189," & ws3a & "!$BI$189," & ws3a & "!$BO$189," & ws3a & "!$BU$189)"
.Name = "Total Cost"
End With
With .SeriesCollection.NewSeries
.Values = "(" & ws3a & "!$G$191," & ws3a & "!$M$191," & ws3a & "!$S$191," & ws3a & "!$Y$191," & ws3a & "!$AE$191," & ws3a & "!$AK$191," & ws3a & "!$AQ$191," & ws3a & "!$AW$191," & ws3a & "!$BC$191," & ws3a & "!$BI$191," & ws3a & "!$BO$191," & ws3a & "!$BU$191)"
.Name = "Overheads"
End With
With .SeriesCollection.NewSeries
.Values = "(" & ws3a & "!$F$191," & ws3a & "!$L$191," & ws3a & "!$R$191," & ws3a & "!$X$191," & ws3a & "!$AD$191," & ws3a & "!$AJ$191," & ws3a & "!$AP$191," & ws3a & "!$AV$191," & ws3a & "!$BB$191," & ws3a & "!$BH$191," & ws3a & "!$BN$191," & ws3a & "!$BT$191)"
.Name = "Annuity Income"
End With
With .SeriesCollection.NewSeries
.Values = "(Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Sett ings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings! $C$3,Settings!$C$3)"
.Name = "=Settings!$B$3"
End With
With .SeriesCollection.NewSeries
.Values = "(Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Sett ings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings! $C$4,Settings!$C$4)"
.Name = "=Settings!$B$4"
End With
End With
Cheers
TimboTom
I'm using the below code to generate a chart however this chart seems to generate a extra series. Here is what shows in the formula when selecting the series:
=SERIES(Menu!$B$5,Menu!$A$6:$A$17,Menu!$B$6:$B$17,1)
Here is my code I have created and using:
With Charts.Add ' Make new chart
' Set its properties
.ChartType = xlLine
.Location Where:=xlLocationAsNewSheet, Name:="C" & Right(ws3a, 4)
.Move After:=Sheets(ws3a)
With .SeriesCollection.NewSeries
.XValues = "(" & ws3a & "!$D$3," & ws3a & "!$J$3," & ws3a & "!$P$3," & ws3a & "!$V$3," & ws3a & "!$AB$3," & ws3a & "!$AH$3," & ws3a & "!$AN$3," & ws3a & "!$AT$3," & ws3a & "!$AZ$3," & ws3a & "!$BF$3," & ws3a & "!$BL$3," & ws3a & "!$BR$3)"
.Values = "(" & ws3a & "!$F$189," & ws3a & "!$L$189," & ws3a & "!$R$189," & ws3a & "!$X$189," & ws3a & "!$AD$189," & ws3a & "!$AJ$189," & ws3a & "!$AP$189," & ws3a & "!$AU$189," & ws3a & "!$AU$189," & ws3a & "!$AV$189," & ws3a & "!$BA$189," & ws3a & "!$BA$189," & ws3a & "!$BB$189," & ws3a & "!$BH$189," & ws3a & "!$BN$189," & ws3a & "!$BT$189)"
.Name = "Total Income"
End With
With .SeriesCollection.NewSeries
.Values = "(" & ws3a & "!$G$189," & ws3a & "!$M$189," & ws3a & "!$S$189," & ws3a & "!$Y$189," & ws3a & "!$AE$189," & ws3a & "!$AK$189," & ws3a & "!$AQ$189," & ws3a & "!$AW$189," & ws3a & "!$BB$189," & ws3a & "!$BH$189," & ws3a & "!$BB$189," & ws3a & "!$BC$189," & ws3a & "!$BH$189," & ws3a & "!$BI$189," & ws3a & "!$BO$189," & ws3a & "!$BU$189)"
.Name = "Total Cost"
End With
With .SeriesCollection.NewSeries
.Values = "(" & ws3a & "!$G$191," & ws3a & "!$M$191," & ws3a & "!$S$191," & ws3a & "!$Y$191," & ws3a & "!$AE$191," & ws3a & "!$AK$191," & ws3a & "!$AQ$191," & ws3a & "!$AW$191," & ws3a & "!$BC$191," & ws3a & "!$BI$191," & ws3a & "!$BO$191," & ws3a & "!$BU$191)"
.Name = "Overheads"
End With
With .SeriesCollection.NewSeries
.Values = "(" & ws3a & "!$F$191," & ws3a & "!$L$191," & ws3a & "!$R$191," & ws3a & "!$X$191," & ws3a & "!$AD$191," & ws3a & "!$AJ$191," & ws3a & "!$AP$191," & ws3a & "!$AV$191," & ws3a & "!$BB$191," & ws3a & "!$BH$191," & ws3a & "!$BN$191," & ws3a & "!$BT$191)"
.Name = "Annuity Income"
End With
With .SeriesCollection.NewSeries
.Values = "(Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Sett ings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings!$C$3,Settings! $C$3,Settings!$C$3)"
.Name = "=Settings!$B$3"
End With
With .SeriesCollection.NewSeries
.Values = "(Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Sett ings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings!$C$4,Settings! $C$4,Settings!$C$4)"
.Name = "=Settings!$B$4"
End With
End With
Cheers
TimboTom