Excel: Create pivot chart from pivot table (XL2007)

Hi guys, Trying to automate the generation of a pivot chart using VBA Have managed to create the pivot table fine - all fields set up (pages, rows, columns, data) and it looks fine. I now want to create a pivot chart based on this pivot table. I'm running into trouble when I try to set the source data for the chart :

Dim objWorksheetPivotTable As Excel.Worksheet
Dim objPivotCache As Excel.PivotCache
Dim objPivotTable As Excel.PivotTable
Dim objPivotChart As Variant
With objWorksheetPivotTable
Set objPivotChart = .Shapes.AddChart
With objPivotChart
        .SetSourceData objPivotTable.TableRange1
        .ChartType = xlColumnClustered
        .Location xlLocationAsNewSheet, "Pivot Chart"
    End With
End With
I get the following error :
Error 438 : Object doesn't support this property or method
I've tried defining objPivotChart a number of ways (as a Chart, as a Shape - I'm just using Variant now so I can let Excel assign it to suit) How do I create my pivot chart, based on the existing pivot table? Thanks Al

This question generated 14 answers. To proceed to the answers, click here.

This thread is current as of November 20, 2014.

For more resources for Microsoft Excel