Problem with using a Macro for creating a chart

[aa123]

I'm using Excel 2003 and I'm trying to automate some monthly report charts.

I'd like to run a macro that will get data from several different cells, along the same column, and put them into a 2-type chart, which will be saved in a separate Excel workbook. Then data from cells exactly one row below will be collected, put into a 2-type chart, and then saved into a separate Excel workbook. Repeat for multiple rows of data. The reason it's a 2-type chart is because I want a line graph chart type due to the need for a target line.

Here is an example image of the chart. I've created the chart in Excel but I have no way to upload the picture at work, as all image hosting sites are seemingly blocked:

My chart only has one series though, other than the target series, so it's not as complex as the above.

I've never used Macros in Excel before, and I'm having trouble with them. I've recorded a Macro while going through the process but I'm receiving the following error:

Run-time error '1004':
Method 'SeriesCollection' of object '_Chart' failed

I highlighted the erroneous code in red:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded
'
'
    Charts.Add
    [COLOR=red]ActiveChart.ChartType = xlColumnClustered[/COLOR]
[COLOR=red]ActiveChart.SeriesCollection(1).XValues = _[/COLOR]
[COLOR=red]  "=(Sheet1!R42C2,Sheet1!R42C5,Sheet1!R42C8,Sheet1!R42C11,Sheet1!R41C14,Sheet1!R41C14,Sheet1!R42C14)"[/COLOR]
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    Windows("macro thing.xls").SmallScroll Down:=21
    ActiveSheet.Shapes("Chart 6").IncrementLeft -8.25
    ActiveSheet.Shapes("Chart 6").IncrementTop 476.25
    Windows("macro thing.xls").SmallScroll Down:=39
    ActiveSheet.Shapes("Chart 6").IncrementLeft -345.75
    ActiveSheet.Shapes("Chart 6").IncrementTop 420.75
    Windows("macro thing.xls").SmallScroll Down:=12
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""Conv Rate"""
    ActiveChart.SeriesCollection(2).Values = _
        "=(Sheet1!R116C9,Sheet1!R117C9,Sheet1!R118C9,Sheet1!R119C9,Sheet1!R120C9)"
    ActiveChart.SeriesCollection(2).Name = "=""Target"""
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ChartType = xlLine
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).AxisGroup = 2
    ActiveChart.ChartArea.Select
    With ActiveChart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlCategory, xlSecondary) = True
        .HasAxis(xlValue, xlPrimary) = True
        .HasAxis(xlValue, xlSecondary) = True
    End With
    ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
    ActiveChart.Axes(xlCategory, xlSecondary).CategoryType = xlAutomatic
    ActiveChart.Axes(xlCategory, xlSecondary).Select
    With ActiveChart.Axes(xlCategory, xlSecondary)
        .Crosses = xlMaximum
        .TickLabelSpacing = 1
        .TickMarkSpacing = 1
        .AxisBetweenCategories = False
        .ReversePlotOrder = False
    End With
    With Selection.Border
        .Weight = xlHairline
        .LineStyle = xlAutomatic
    End With
    With Selection
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
        .TickLabelPosition = xlNone
    End With
    ActiveChart.Axes(xlValue, xlSecondary).Select
    With ActiveChart.Axes(xlValue, xlSecondary)
        .MinimumScaleIsAuto = True
        .MaximumScale = 1
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlMaximum
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    ActiveChart.PlotArea.Select
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScaleIsAuto = True
        .MaximumScale = 1
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    ActiveChart.Axes(xlValue, xlSecondary).Select
    With Selection.Border
        .Weight = xlHairline
        .LineStyle = xlAutomatic
    End With
    With Selection
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
        .TickLabelPosition = xlNone
    End With
End Sub

I'm sure there'll be some superfluous data in there, as this is the code directly after I recorded the macro.

Any help would be much appreciated, thanks.

 

[Andrew Poulsom]

Your code compiled fine for me. What error are you getting?

 

[aa123]

I'm receiving this error:

Run-time error '1004':
Method 'SeriesCollection' of object '_Chart' failed

And the following is highlighted in yellow when I press debug:

ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).XValues = _
 "=(Sheet1!R42C2,Sheet1!R42C5,Sheet1!R42C8,Sheet1!R42C11,Sheet1!R41C14,Sheet1!R41C14,Sheet1!R42C14)"

Does it compile the chart completely for you, as in with both axis and both types of charts?

Thanks for the quick reply.

 

[Andrew Poulsom]

That's because you haven't added a series (or rather the macro recorder hasn't recorded it):

ActiveChart.SeriesCollection.NewSeries

 

[aa123]

I did what you advised and pasted in the "ActiveChart.SeriesCollection.NewSeries" code into the original code I posted. It got it past that stage, then gave me some sort of error about small scrolling. So I deleted those parts, ran it again and it kind of worked, but it wasn't using all the specified data.

I recorded a completely new macro, and now it seems to be working perfectly!

Here's the code for what is currently working:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 31/08/2011 by
'
'
    ActiveWindow.SmallScroll Down:=-24
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
        "D44,G44,J44,M44,P44"), PlotBy:=xlRows
    ActiveChart.SeriesCollection(1).XValues = _
        "=(Sheet1!R42C2,Sheet1!R42C5,Sheet1!R42C8,Sheet1!R42C11,Sheet1!R42C14)"
    ActiveChart.SeriesCollection(1).Name = "=""Conversion Rate"""
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Conversion Rate"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Conversion %"
    End With
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Values = _
        "=(Sheet1!R116C9,Sheet1!R117C9,Sheet1!R118C9,Sheet1!R119C9,Sheet1!R120C9)"
    ActiveChart.SeriesCollection(2).Name = "=""Target"""
    Windows("macro thing.xls").SmallScroll Down:=-21
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ChartType = xlLine
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).AxisGroup = 2
    ActiveChart.ChartArea.Select
    With ActiveChart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlCategory, xlSecondary) = True
        .HasAxis(xlValue, xlPrimary) = True
        .HasAxis(xlValue, xlSecondary) = True
    End With
    ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
    ActiveChart.Axes(xlCategory, xlSecondary).CategoryType = xlAutomatic
    ActiveChart.Axes(xlCategory, xlSecondary).Select
    With ActiveChart.Axes(xlCategory, xlSecondary)
        .Crosses = xlMaximum
        .TickLabelSpacing = 1
        .TickMarkSpacing = 1
        .AxisBetweenCategories = False
        .ReversePlotOrder = False
    End With
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScaleIsAuto = True
        .MaximumScale = 1
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    ActiveChart.Axes(xlValue, xlSecondary).Select
    With ActiveChart.Axes(xlValue, xlSecondary)
        .MinimumScaleIsAuto = True
        .MaximumScale = 1
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlMaximum
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    With Selection.Border
        .Weight = xlHairline
        .LineStyle = xlAutomatic
    End With
    With Selection
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
        .TickLabelPosition = xlNone
    End With
    ActiveChart.Axes(xlCategory, xlSecondary).Select
    With Selection.Border
        .Weight = xlHairline
        .LineStyle = xlAutomatic
    End With
    With Selection
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
        .TickLabelPosition = xlNone
    End With
End Sub

Is there anything different in that code that would explain why it's working? It seems to be extremely temperamental, so I'm just trying to learn as much as I can, in case I have to fix it again next time!