Create a simple chart in a sheet where a pivot table already exists

[Butcherk]

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]

hi. try this:

Worksheets("Overview").Range("$B$26:$E$28")

[Butcherk]

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]

did you try recording a macro while creating the PT?

[Butcherk]

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]

can you post a workbook with sample data?

[Butcherk]

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.