CODEWITHSUNDEEP
excelvbachartspivot
I Forte Si Daja
I Forte Si Daja

Reputation: 45

Create a chart for each pivot table

In my active sheet I have two Pivot table (PivotTable1 and PivotTable2) sometimes they could be more though. I am trying to add a chart for each PT in the active sheet. So far I came up with this code but it is giving me 4 charts 3 blank ones and a correct one if one of the PT is selected.

Sub CreateChart1()

Dim pivot As PivotTable, sh As Worksheet, nrp As Long

Set sh = ThisWorkbook.Sheets("Sheet1")

For nrp = 1 To sh.PivotTables.Count

 Set pivot = ActiveSheet.PivotTables(nrp)
    For Each pivot In sh.PivotTables
        Charts.Add
        ActiveChart.Location Where:=xlLocationAsObject, Name:=pivot.Parent.Name
    Next pivot
Next nrp

End Sub

Can somebody explain to me what am I doing wrong?

Any help is appreciated.

Thanks

Nick.

Upvotes: 0

Views: 389

Answers (1)

BigBen
BigBen

Reputation: 50162

You are getting four charts even though you only have two pivot tables because you have two loops and only need one.

The inner loop For Each pivot In sh.PivotTables loops through each pivot table, as does the outer loop For nrp = 1 To sh.PivotTables.Count. So you'll get 2 charts for each pivot table.

Instead of using Charts.Add I suggest using ChartObjects.Add, which creates an embedded chart and where you can control the placement and size.

Then you also need to SetSourceData.

An example might look like this:

Sub AddPivotChart()
    Dim pivotTbl As PivotTable, ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet2")

    For Each pivotTbl In ws.PivotTables
        Dim chtObj As ChartObject
        Set chtObj = ws.ChartObjects.Add(50, 50, 200, 200) ' adjust as needed

        chtObj.Name = pivotTbl.Name
        chtObj.Chart.SetSourceData pivotTbl.TableRange1
    Next pivotTbl
End Sub

Upvotes: 3

Related Questions