help creating charts with VBA

[cornev]

Great site and learned a lot alreay, thanks!

Now I need to create multiple pie charts based on data in tables on many sheets.

I need to create the same set of charts, for each sheet separately.

I recorded a macro for one sheet, which created following code:

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$B$2"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$D$6:$D$8"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$A$6:$A$8"
Range("A11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$6"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$6:$C$6"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"
ActiveWindow.SmallScroll Down:=3
Range("A11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$7"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$7:$C$7"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"

However I cannot automate. I tried different methods for creating the charts, as found on the site [prefer .chartobjects.add(x,y,z,a) since I can immediately position the chart] , but get stuck at seriescollection everytime.

It looks like when the chart is created by XL (with suggested data in it) I cannot address the series data.
I got it right to generate a chartobject that is empty, then added the series data to it, but when opening the new chartobject, it is again populated with suggested data.

How do I generate an empty chartobject everytime, so I can insert seriescollection info with code?

Any other way to generate charts?

 

[cornev]

@Norie: Sorry, no intention to offend

Get runtime error 1004 with your code though.

 

[cornev]

edit: more information.

I ran this code through another spreadsheet, and can create 5 graphs in succession with no problem. On this sheet, I do have problems.
THe sheet I get errors, contain the data in a pivottable. Error is "Method 'Add' of object 'SeriesCollection' failed"

Is there different way to address fields in the pivottable, to generate charts? Note that the charts I try to create is not what you would get for pivottables normally.

Hope this helps!

 

[cornev]

Bump

 

[Jon Peltier]

Old thread, but here's the most likely problem:

The code works in a sheet with regular data.

The code fails in a sheet with data from a pivot table.

If the selected range overlaps the pivot table, the chart becomes a pivot chart, and you cannot modify the series data or the overall data range of a pivot chart.

You need to select a blank cell a distance away from the pivot table prior to creating the chart to ensure you're creating a regular chart from the pivot table.