VBA create multiple pivot charts

[rhombus4]

I can create a pivot chart from a pivot table, but sometimes when it creates the 2nd or 3rd one I get an error

Highlights ch.SetSourceData pt.TableRange1
'-2147467259(80004005): method 'setsourcedata' of object'_chart failed

Set pt = mysheet.pivottables("pivot1")

Set chobj = mysheet.ChartObjects.Add(300, 200, 550, 200)

Set ch = chobj.Chart
ch.SetSourceData pt.TableRange1
ch.ChartType = xl3DColum

Set pt = mysheet.pivottables("pivot2")

Set chobj = mysheet.ChartObjects.Add(600, 200, 550, 200)

Set ch = chobj.Chart
ch.SetSourceData pt.TableRange1
ch.ChartType = xlbarclustered

 

[Domenic]

Make sure that the active cell in your workheet does not reside within the range of any pivot table when you run your macro. Otherwise, when you add the chartobject to your worksheet, it automatically sets the range for the source data to that of the pivot table where the active cell resides. Then, when you try to set the range for the source data, you'll get that error.

Better yet, in your code, add a new worksheet and create your pivot charts in this new sheet so you won't have to worry about where the active cell resides.

Hope this helps!

 

[rhombus4]

Thanks Domenic

Didn't think cell was active within pivot table

Looks like any cell that surrounds any part of any pivot table affects it as well

I.e. if pivot table range is e2:f20 if active cell is in e1:G1 or G2:G20 it I'll give the error

 

[Domenic]

You're very welcome!

Cheers!