CODEWITHSUNDEEP
excelvba
Rick Prieto Palacin
Rick Prieto Palacin

Reputation: 31

A new chart every sheet

I'm trying to add a new chart every sheet based on some information I already have. However, I end up having just one sheet with the chart for the last piece of information. I think the charts are overwriting themselves.

Here's the code

Dim chart1 As chart
Set chart1 = Charts.Add

For i = 0 To 9

    chart1.Add.SetSourceData Source:=Destino.Range("A24").Offset(0, 3 * i).CurrentRegion, PlotBy:=xlRows
    chart1.ChartType = xlBarClustered
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="gráfico" & i + 1

    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Cuenta por categoría de" & origen.[c4].Offset(i, 0)
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Categoría"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Frecuencia"
    End With

    ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)

Next

Upvotes: 2

Views: 70

Answers (1)

Michael Chad
Michael Chad

Reputation: 425

In your example, you declare and set the Chart object outside of the loop. So it's the same instance of Chart in each iteration of the loop, and it does essentially get over-written, or more accurately, updated.

Try moving them inside the loop, and declare the Charts object as New. Now each time it increments, it will generate a NEW instance of Chart. Otherwise, everything looks good.

For i = 0 To 9

    Dim chart1 As New Chart
    Set chart1 = Charts.Add

    chart1.Add.SetSourceData Source:=Destino.Range("A24").Offset(0, 3 * i).CurrentRegion, PlotBy:=xlRows
    chart1.ChartType = xlBarClustered
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="gráfico" & i + 1

    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Cuenta por categoría de" & origen.[c4].Offset(i, 0)
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Categoría"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Frecuencia"
    End With

    ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)

Next

Upvotes: 1

Related Questions