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)
‘Add the chart.
‘Place it on the PivotTable’s worksheet.
ActiveChart.Location Where:=xlLocationAsObject, _
‘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.
‘Turn on ScreenUpdating.
Application.ScreenUpdating = True
Watch the video:
Using VBA to create pivot-table and pivot-chart