Excel Vba Help Required

[JonathanParker]

Hello,

Wondered if you could help me with a little issue I'm having. I'm exporting some data from Access to Excel and converting into some fancy graphs. The number of series' ranges from 2 to 5. Below isa copy of the code I am using to format the graph in terms of series names, layout, colours of lines, etc. When the full 5 series are there to be charted there is no problem with the code, however, when I try to convert less the 5 series I get an error message saying error code 400. I have tried to trap the error and it seems to be when I try to name the values on the X-axis. I've tried to take that line out but I can't seem to get it to work. Any help would be greatly appreciated.

Sub AutoExec()
Dim budgetusage As Boolean, actualcost As Boolean, budgetcost As Boolean
Dim row As Integer, series As Integer
Dim sheet_name As String, i As Integer

'MsgBox "auto"
On Error GoTo last_sheet
While 1
ActiveSheet.Next.Select
Wend
last_sheet:
sheet_name = ActiveSheet.Name
On Error GoTo 0

Sheets(sheet_name).Select
Range("A1").Select
Selection.End(xlDown).Select
row = ActiveCell.row

Cells(row + 1, 4).Formula = "=SUM(D2:D" & row & ")"
budgetusage = (Cells(row + 1, 4).Value <> 0)

Cells(row + 1, 5).Formula = "=SUM(E2:E" & row & ")"
actualcost = (Cells(row + 1, 5).Value <> 0)

Cells(row + 1, 6).Formula = "=SUM(F2:F" & row & ")"
budgetcost = (Cells(row + 1, 6).Value <> 0)

Sheets("GraphicalAnalysisWeek").Select
ActiveChart.ChartArea.Select
Sheets("GraphicalAnalysisWeek").Copy after:=Sheets(sheet_name)
Sheets(ActiveSheet.Name).Name = sheet_name & " chart"

series = 1
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(series).Values = "='" & sheet_name & "'!R2C2:R" & row & "C2"
ActiveChart.SeriesCollection(series).Name = "=""Production Volume"""

series = 2
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(series).Values = "='" & sheet_name & "'!R2C3:R" & row & "C3"
ActiveChart.SeriesCollection(series).Name = "=""Usage Volume"""

series = 3

If budgetusage Then
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(series).Values = "='" & sheet_name & "'!R2C4:R" & row & "C4"
ActiveChart.SeriesCollection(series).Name = "=""Budget Usage"""
series = series + 1
End If

If actualcost Then
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(series).Values = "='" & sheet_name & "'!R2C5:R" & row & "C5"
ActiveChart.SeriesCollection(series).Name = "=""Actual Cost"""
series = series + 1
End If

If budgetcost Then
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(series).Values = "='" & sheet_name & "'!R2C6:R" & row & "C6"
ActiveChart.SeriesCollection(series).Name = "=""Budget Cost"""
End If
MsgBox ""

For i = 1 To series
ActiveChart.SeriesCollection(i).XValues = "='" & sheet_name & "'!R2C1:R" & row & "C1"
Next i

MsgBox ""

ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.Axes(xlCategory).Select
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With

ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Font.Bold = True
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=1
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 35
.Fill.BackColor.SchemeColor = 2
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlMedium
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
With Selection.Border
.ColorIndex = 16
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Utilities Tracker"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume"
.Axes(xlCategory, xlSecondary).HasTitle = True
.Axes(xlCategory, xlSecondary).AxisTitle.Characters.Text = "£"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.ChartArea.Select
With ActiveChart
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "£"
End With
ActiveChart.SeriesCollection(5).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 41
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(5).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 4
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 6000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

End Sub