Create a chart for each pivot table

Question

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.

BigBen · Accepted Answer · 2019-02-10 20:45:57Z

3

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

answered Feb 10, 2019 at 20:45

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I Forte Si Daja Over a year ago

Hi Big Ben Thanks a lot buddy

Collectives™ on Stack Overflow

Create a chart for each pivot table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related