using automation in excel to create multiple charts

[lizjohn1]

My problem is pretty simple:

I want to create several pie charts from rows of data (1 chart
worksheet per row of data).

I know how to do this for 1 chart, but how can I pass in a range to a
routine so as all of the charts I need are created at once (in some
sort of loop) ?

Thanks in advance.

 

[Jon Peltier]

This does the trick. Select a range with categories (legend entries) in the
top column and series names (chart title) in the first column. Adjust size
and position through the parameters in the ChartObjects.Add() method.

Sub OnePieChartPerRow()
Dim rngChartData As Range
Dim iRowIx As Integer, iRowCt As Integer, iColCt As Integer
Dim oChart As ChartObject
Dim NewSrs As Series

If Not TypeName(Selection) = "Range" Then Exit Sub

Set rngChartData = Selection
iRowCt = rngChartData.Rows.Count
iColCt = rngChartData.Columns.Count

For iRowIx = 2 To iRowCt
Set oChart = ActiveSheet.ChartObjects.Add(Top:=25 + (iRowIx - 2) * 200,
_
Height:=200, Left:=450, Width:=300)
Set NewSrs = oChart.Chart.SeriesCollection.NewSeries
oChart.Chart.ChartType = xlPie
With oChart.Chart.PlotArea
.Border.LineStyle = xlNone
.Interior.ColorIndex = xlNone
End With
With NewSrs
'' Name in first column
.Name = rngChartData.Cells(iRowIx, 1)
.Values = rngChartData.Cells(iRowIx, 2).Resize(1, iColCt - 1)
'' XValues in first row
.XValues = rngChartData.Cells(1, 2).Resize(1, iColCt - 1)
End With
Next

End Sub


- Jon

 

[lizjohn1]

I will try it (but now have some other urgent work to do). I am
assuming that if I want to make all the charts into their own sheets, I
can just say "As ChartSheet" instead of "As ChartObject".

Thanks a lot!

 

[Jon Peltier]

To do them as chart sheets, you need to make a few changes (see lines
between v v v and ^ ^ ^

Sub OnePieChartPerRow()
Dim rngChartData As Range
Dim iRowIx As Integer, iRowCt As Integer, iColCt As Integer
v v v v v v v v v v v v v v v v v v v v
Dim oChart As Chart
^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
Dim NewSrs As Series

If Not TypeName(Selection) = "Range" Then Exit Sub

Set rngChartData = Selection
iRowCt = rngChartData.Rows.Count
iColCt = rngChartData.Columns.Count

For iRowIx = 2 To iRowCt
v v v v v v v v v v v v v v v v v v v v
Set oChart = Charts.Add
Set NewSrs = oChart.SeriesCollection.NewSeries
oChart.ChartType = xlPie
With oChart.PlotArea
^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
.Border.LineStyle = xlNone
.Interior.ColorIndex = xlNone
End With
With NewSrs
'' Name in first column
.Name = rngChartData.Cells(iRowIx, 1)
.Values = rngChartData.Cells(iRowIx, 2).Resize(1, iColCt - 1)
'' XValues in first row
.XValues = rngChartData.Cells(1, 2).Resize(1, iColCt - 1)
End With
Next

End Sub

- Jon

 

[lizjohn1]

Hi,

I tried this; however, I get errors in the last With NewSrs block.
When I comment that out, I get a pie chart of the data in the first
column (not the first row), and single value pie charts for the rest of
the rows.

my data looks like this:

Pie Chart Name Field1 Field2 Field3 ... FieldN
Chart1 Name val1 val2 val3 ... valN
Chart2 Name val1 val2 val3 ... valN

ChartN Name val1 val2 val3 ... valN

So I am getting a pie with all the val1's instead of from val1 - valN
in Row Chart1 Name.

Thanks for any assistance you can provide.