VBA to Drill Down in a PivotChart - Error 91

[pjmorris]

HI,

As part of a project I want to use VBA to drill down in a pivotchart. I've referred to the documentation here: MicrosoftDocs - Pivotchart Drilldown and the code snippet provided there:

ActiveChart.PivotLayout.PivotTable.DrillDown ActiveChart.PivotLayout.PivotTable _
      .PivotFields("[Customer].[Customer Geography].[Country]").PivotItems( _
      "[Customer].[Customer Geography].[Country].&[Australia]"), ActiveChart. _
      PivotLayout.PivotTable.PivotRowAxis.PivotLines(1)

is very similar to the code I've recorded:

Sub Macro6()
'
' Macro6 Macro
'
    ActiveSheet.ChartObjects("chtDataEntry2").Activate
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.PivotLayout.PivotTable.DrillDown ActiveChart.PivotLayout.PivotTable _
        .PivotFields("[qryCategories].[Category].[Category]").PivotItems( _
        "[qryCategories].[Category].[Category].&[Fuel/Oil]"), ActiveChart.PivotLayout.PivotTable. _
        PivotRowAxis.PivotLines(8)
End Sub

But when I run Macro6 i get an Error 91 - 'Object Variable or With Block Variable not set'. I know the correct chart has been activated because the error only occurs on the ActiveChart.PivotLayout.PivotTable.DrillDown line.

Any ideas gratefully received.

 

[pjmorris]

for avoidance of duplication - please note that I've posted the same question on StackOverflow.com here

 

[pjmorris]

bump - please any ideas?