PDA

View Full Version : Create a simple chart in a sheet where a pivot table already exists



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!

mancubus
03-11-2014, 05:17 AM
hi. try this:
Worksheets("Overview").Range("$B$26:$E$28")

Butcherk
03-11-2014, 05:39 AM
Hi,
Unfortunately, this returns the same error. And again, it seems that the chart created is automatically a pivot chart using the same filters, rows, columns, and values as the pivot table already present in the sheet.

mancubus
03-11-2014, 06:02 AM
did you try recording a macro while creating the PT?

Butcherk
03-11-2014, 06:15 AM
In fact, I do create the pivot table using VBA as well.
I did try to create the simple chart in question by recording a macro. However, when copying the code onto my module, it does not execute (same error as shown earlier). As a matter of fact, the macro does not even execute if I simply delete the chart and run the recorded macro.

mancubus
03-11-2014, 06:26 AM
can you post a workbook with sample data?

Butcherk
03-11-2014, 07:05 AM
Well, I did find a solution but it is a bit dirty. Instead of using a ChartObject, I use the "ActiveChart" object to assign the range of my chart. This and rebooting my CPU seems to have done the trick... Not very satisfactory though.