Add a chart on excel with vba

Question

I'm trying to add a Scatter chart on a sheet, when I doit manually it works just fine, but when I try to use the recorded macro it give me an 1004 error on

" Sheets("Mapa Riesgos").Shapes.AddChart.Select "

here's the code

Sheets("Mapa Riesgos").Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range("A11:B35")
With ActiveChart
    .Parent.name = "Riesgo Inherente"
    .Legend.Delete
    .Axes(xlValue).MinimumScale = 0.5
    .Axes(xlValue).MaximumScale = 5.5
    .Axes(xlCategory).MinimumScale = 0.5
    .Axes(xlCategory).MaximumScale = 5.5
    .Axes(xlValue).MajorGridlines.Delete
    .Axes(xlValue).Delete
    .Axes(xlCategory).Delete
    .SeriesCollection(1).MarkerStyle = -4142

     'chart name
    .HasTitle = True
    .ChartTitle.Characters.Text = "Riesgo Inherente"
    .ChartTitle.Font.Size = 20
     'X axis name
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 13
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Impacto"
     'y-axis name
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 13
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Probabilidad"

    .Parent.Width = 400
    .Parent.Height = 400
    .Parent.Left = 60
    .Parent.Top = 100
End With

I've used this code before, but for some reason when I used here it work just the first run and then the error start appearing

Answer 1

If your triggering it from another sheet you'll need to update the source data line to include the sheet name like this:

ActiveChart.SetSourceData Source:=Range("'Mapa Riesgos'!A11:B35")

---

Also, if your coming from another sheet you'll need to set your chart sheet as active in order to use ActiveChart. Do this before you create your chart.

Sheets("Mapa Riesgos").Select