Generating Defects Chart using VBA

[jonathansanabria]

Hi,

I would like to generate a chart that displays the top 5 defects for a particular machining process. However, the current code I have fails when trying to run...Here's my current code:

' Generate Top 5 Defects Chart
    Sheets("FrontEnd").Select
    ' Select data range of top 5 defect names and quantities
    Range("B6:B10,M6:M10").Select
    Range("M6").Activate
    ' Generate chart
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range( _
        "FrontEnd!$B$6:$B$10,FrontEnd!$M$6:$M$10")
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Top5Defects"
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.FullSeriesCollection(1).ApplyDataLabels
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    ActiveChart.SetElement (msoElementPrimaryValueGridLinesMinorMajor)
    Sheets("Top5Defects").Select
    ' Add title, axes, and formatting
    ActiveChart.ChartTitle.Select
    Selection.Caption = "=Background!R2C5"
    Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    Selection.Format.TextFrame2.TextRange.Font.Size = 20
    With ActiveChart
    .Axes(xlValue, xlPrimary).HasTitle = True
    End With
    ActiveChart.Axes(xlValue).AxisTitle.Select
    Selection.Caption = "=Background!R3C5"
    Selection.Format.TextFrame2.TextRange.Font.Size = 14
    Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).TickLabels.Font.Bold = msoTrue
    ActiveChart.Axes(xlValue).TickLabels.Font.Size = 12
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).TickLabels.Font.Bold = msoTrue
    ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 12
    Sheets("Top5Defects").Select
    Sheets("Top5Defects").Move Before:=Sheets(3)

The worksheet looks as follows:

I'd simply like to plot "B6:B10,M6:M10" and then format it.
Help would be greatly appreciated!

Thanks,
JT