Butcherk
03-11-2014, 02:58 AM
Hello,
So, here is the situation: I have a Sheet on which I created a pivot table based on some data from another sheet as well as a normal table (based on the pivot table, using the GETPIVOTDATA function). I would like to create a simple chart based on the normal table using VBA. Now, the following procedure automatically creates a pivot chart based on the pivot table in my sheet:
Dim myChart As ChartObject
Set myChart = Worksheets(OVERVIEW).ChartObjects.Add(Left:=100, Width:=375, Top:=100, Height:=225)
With myChart.Chart
.ChartType = xlColumnStacked
.SetSourceData Source:=Range("Overview!$B$26:$E$28") <---------------------- ERROR HERE
End With
And what I really would like to do is prevent Excel from automatically creating a pivot chart based on the pivot table present in my sheet, or allow me to redefine the type of the chart and the source data as I wish. Right now, when the line ".SetSourceData Source:=Range("Overview!$B$26:$E$28")" is executed, Excel returns the error "Run-time error '-2147467259 (80004005)': Method 'SetSourceData' of object '_Chart' failed".
I've been looking around but have not found any explanation I could understand regarding this error. I assume this simply tells me that I'm not allowed to re-allocate the source data, but that is pure speculation.
Any help would be much appreciated!
So, here is the situation: I have a Sheet on which I created a pivot table based on some data from another sheet as well as a normal table (based on the pivot table, using the GETPIVOTDATA function). I would like to create a simple chart based on the normal table using VBA. Now, the following procedure automatically creates a pivot chart based on the pivot table in my sheet:
Dim myChart As ChartObject
Set myChart = Worksheets(OVERVIEW).ChartObjects.Add(Left:=100, Width:=375, Top:=100, Height:=225)
With myChart.Chart
.ChartType = xlColumnStacked
.SetSourceData Source:=Range("Overview!$B$26:$E$28") <---------------------- ERROR HERE
End With
And what I really would like to do is prevent Excel from automatically creating a pivot chart based on the pivot table present in my sheet, or allow me to redefine the type of the chart and the source data as I wish. Right now, when the line ".SetSourceData Source:=Range("Overview!$B$26:$E$28")" is executed, Excel returns the error "Run-time error '-2147467259 (80004005)': Method 'SetSourceData' of object '_Chart' failed".
I've been looking around but have not found any explanation I could understand regarding this error. I assume this simply tells me that I'm not allowed to re-allocate the source data, but that is pure speculation.
Any help would be much appreciated!