Add Pivot Chart to Excel Worksheet Using VBA

Once we have our Pivot Table we may like to place a chart next to it because a picture is worth a thousand words. Using VBA we can add a pivot chart to our Excel worksheet quickly and easily. The Pivot table becomes our data source and the location of the chart becomes our parent or active worksheet. We can also define the exact location of the chart with respect to our data of the pivot table. The complete VBA code attached to a command button is given below:

Private Sub CommandButton3_Click()
‘We turn off ScreenUpdating to help our macro run faster
Application.ScreenUpdating = False
‘we Declare a variable for the PivotTable
Dim myPT As PivotTable
‘we set the myPT variable for our first PivotTable using index no. 1
Set myPT = ActiveSheet.PivotTables(1)
‘Select PivotTable.
myPT.PivotSelect (“”)
‘Add the chart.
Charts.Add
‘Place it on the PivotTable’s worksheet.
ActiveChart.Location Where:=xlLocationAsObject, _
Name:=myPT.Parent.Name
‘Position the PivotChart so its top left corner
‘occupies cell H23, a few rows below the PivotTable.
ActiveChart.Parent.Left = Range(“E14”).Left
ActiveChart.Parent.Top = Range(“E14”).Top
‘Deselect the PivotChart.
Range(“A1”).Select
‘Turn on ScreenUpdating.
Application.ScreenUpdating = True

End Sub

Watch the video:


Further reading:
Using VBA to create pivot-table and pivot-chart

Watch this video on Youtube

One thought on “Add Pivot Chart to Excel Worksheet Using VBA

  1. Alexander Katz

    How would I create a pivot table from multiple tabs using the same rows? For example I have 100 tabs in a document and I am going to use only the data from A1 to B6 to make my pivot table.

    Any help would be greatly appreciated.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *