AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 660
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
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 :
I get the following error :
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
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 :
Code:
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
[COLOR=#ff0000].SetSourceData objPivotTable.TableRange1[/COLOR]
.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