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?

 

[Norie]

I was able to use this to create a chart based on, I think anyway, the data in the ranges you appear to want to use.

Dim cht As ChartObject
    
        Set cht = ActiveSheet.ChartObjects.Add(100, 100, 100, 100)
    
        With cht.Chart
    
            .ChartType = xlPie
    
            .SeriesCollection.Add ActiveSheet.Range("D6:D8")
        
            .SeriesCollection(1).XValues = ActiveSheet.Range("A6:A8")
            
            .SeriesCollection(1).Name = ActiveSheet.Range("B2")
            
        End With

I'm pretty sure this doesn't solve your problem but I'm not actually sure what that is.

 

[lilredsrt]

Here's something I picked up here from rorya that works pretty well for my automated chart building.

I've found that upon creating the chart I give it a specific name so that I can make reference to it and it's specific data series. For my specific needs I also place charts on different sheet names.

Dim LiqChart As Chart
Set LiqChart = Charts.Add
chrtNme = dlrnum & "LIQCHART"

'Charts.Add
LiqChart.ChartType = xlColumnStacked100
LiqChart.SetSourceData Source:=Sheets(rptShtNme).Range("I" & rptrow + 27 & ":N" & rptrow + 33), PlotBy:=xlRows

Set LiqChart= LiqChart.Location(Where:=xlLocationAsObject, Name:=rptShtNme)
LiqChart.Parent.Name = chrtNme

Worksheets(rptShtNme).ChartObjects(chrtNme).Width = 325
Worksheets(rptShtNme).ChartObjects(chrtNme).Height = 200

Then you can select the chart and adjust the series.

ActiveSheet.ChartObjects(chrtNme).Activate
ChrSeriesCnt = ActiveChart.SeriesCollection.Count

Does this help?

 

[cornev]

@ Norie: Thanks, but I still get a similar error "Method 'add' of object 'seriescollection' failed"

@lilredsrt: I end up with the same issue - addressing seriescollection

If I use this code, it generates 1 correct piechart:
Set Cht = sht.ChartObjects.Add(50, 200, 400, 200)

With Cht.Chart
Set SerColl = .SeriesCollection
End With

Set SelRange = sht.Range("A6:A10")
'SerColl.NewSeries.Select
With SerColl.NewSeries
.XValues = SelRange
.Values = sht.Range("H6:H10")
.Name = sht.Range("B2").Formula
End With
Cht.Chart.ChartType = xlPie

This creates en empty chartobject and I can add information to it. However, when I begin with the second chart (at set cht = sht.chartobjects....) XL creates a chartobject, with barchart in and I get "Error 1004: Application-defined or object defined error"

SO my thinking is, put in a line of code that initialises chartobject/allows me to create a blank chartobject every time.

Other ideas?

 

[lilredsrt]

Try creating a sub with a loop and passing the value for the chart name and ranges to the function that you created.

Or put your code in a loop.

If I get a sec I'll put one together for you.

 

[lilredsrt]

Oh by the way.
Are you creating multiple charts?
If so how many?

 

[Norie]

Why did you change the code I posted?

If you were to post some data I could test it to see how it needs to be altered to create multiple charts.

 

[cornev]

Thanks lilredsrt

I'm creating 4 pie charts.
I have 3 stores, listed in cells B5-D5.
Row A contain the categories of product - A6-A8.
E5:E8 contain the totals sold for each category.

So I create a pie chart of data in E6:E8, with A6:A8 giving the xvalues

Then a chart per category - B6:D6, with B5:D5 the x-values, and similar for rows 7 and 8.

This is a simplified explanation of the table's size, and this happens for x number of sheets (this part I think I can do already)

 

[lilredsrt]

Edit: so 1 pie chart for each category comparing the 3 stores?

 

[cornev]

jip, plus a totals chart on the data in the last row.

But if can get help in assigning the data, I'm sure it's possible to duplicate the as many times as required.