CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

Error i n creating chart (Double Creation)

I am trying to create a chart on my sheet Monthprepare.

I am using the below code.

I have the code , behind the button with several other calling functions.

The problem is, whenever I am creating the chart, it is creating them twice.

I am left confused what would be the reason.

Could someone help to figure it out. 

Sub chartmonthprep()
Dim cht As Chart
Dim stable As PivotTable
Dim pt, sh
If ActiveSheet.PivotTables.count = 0 Then Exit Sub
Set stable = ActiveSheet.PivotTables(2)
Set pt = stable.TableRange1
Set sh = ActiveSheet.ChartObjects.Add(Left:=250, _
Width:=400, Top:=20, Height:=250)
sh.Select
Set cht = ActiveChart
With cht
.SetSourceData pt
.ChartType = xlColumnStacked
End With
cht.FullSeriesCollection(1).Name = "Average of Red"
cht.SeriesCollection(1).HasDataLabels = True
cht.SeriesCollection(2).HasDataLabels = True
cht.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
cht.SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
cht.HasTitle = True
cht.ChartTitle.Text = " Result"
End Sub

Upvotes: 0

Views: 38

Answers (2)

Rafael Matos
Rafael Matos

Reputation: 258

You can loop for each Pivot Table in a sheet with this:

Sub PivotTable()
    Dim sh As Worksheet
    Dim pvt As PivotTable

    Set sh = ThisWorkbook.Sheets("Sheet1")

    For Each pvt In sh.PivotTables
        MsgBox pvt.Name
        'do something
    Next pvt
End Sub

Upvotes: 0

Vityata
Vityata

Reputation: 43595

Try like this:

Sub chartmonthprep()
    If ActiveSheet.ChartObjects.Count > 1 Then Exit Sub

    'the rest of your code here --v
    Dim cht As Chart
    Dim stable As PivotTable


End Sub

It will make sure that it is only 1 chart.

Upvotes: 1

Related Questions