Microsoft Office Tutorials and References
In Depth Information
Step-by-Step
7.
Set the Object variable for the first (index #1) PivotTable:
Set objPT = ActiveSheet.PivotTables(1)
8.
Select the PivotTable:
objPT.PivotSelect “”
9.
Add the chart:
Charts.Add
10.
Place the chart onto the PivotTable’s worksheet:
ActiveChart.Location Where:=xlLocationAsObject, _
Name:=objPT.Parent.Name
11.
Position the PivotChart so its top-left corner occupies cell H23, a few rows below the
PivotTable:
ActiveChart.Parent.Left = Range(“H23”).Left
ActiveChart.Parent.Top = Range(“H23”).Top
12.
Deselect the PivotChart:
Range(“A1”).Select
13.
Turn on ScreenUpdating:
Application.ScreenUpdating = True
14.
When you have completed the macro, it will look as follows:
Sub CreatePivotChart()
‘Turn off ScreenUpdating.
Application.ScreenUpdating = False
‘Declare an Object variable for the existing PivotTable.
Dim objPT As PivotTable
‘Set the Object variable for the first (index #1) PivotTable.
Set objPT = ActiveSheet.PivotTables(1)
‘Select the PivotTable.
objPT.PivotSelect “”
‘ Add the chart.
Charts.Add
‘Place it on the PivotTable’s worksheet.
ActiveChart.Location Where:=xlLocationAsObject, _
Name:=objPT.Parent.Name
Search JabSto ::




Custom Search