multiple charts

[MBarna]

Hello,

I have 100 series on my spreadsheet and I need to make a chart for each one.
How do I make it in one shot?

I have data ordered in four columns A to D.
A= Serial number
B,C,D= Data

Thanks for your reply.

 

[MBarna]

Thanks for your reply.
In fact i have Excel 2007.

I tried your advice but how do you modify the code?

Thanks again for your answer.

 

[MBarna]

Great, it works.

Another question:
I am trying to make all these charts based on my own template.
When i do so, Excel tells me I need to "debug" and points at me
".SeriesCollection(1).Remove" in the code on Visual Basic.

Would you have a solution for a applying my own template to all the charts?

Thank you.
Regards,

MBarna.

 

[Andy Pope]

My bad.

Change that line of code to

..SeriesCollection(1).Delete

To apply your own template then the following should do it. Obviously
replace path reference with one suitable for you.

objChart.Chart.ApplyChartTemplate ( _
"C:\Users\andy.DIGITAB\AppData\Roaming\Microsoft\Templates\Charts\Chart1.crtx")



Cheers
Andy

 

[MBarna]

Hi again,

Now the problem is:
a message of error is appearing in Visual Basic when I try to apply the
macro saying: "Compile error. Expected function or variable" and it points at
ApplyChartTemplate

(Visual Basic is also rejecting underscore "_" telling it's an Invalid
Character.)

Here's what I put in the code:

With objChart.Chart.ApplyChartTemplate("C:\Documents
andSettings\MBarna\Application
Data\Microsoft\Templates\Charts\Availability.crtx")

Thank you in advance for your answer.

Cheers,
MBarna

 

[Andy Pope]

This code be a long drawn out process trying to debug the code line by line,
especially with the wrapping of code causing you problems.

Email me the file, off newsgroup, and I will have a look see.
You will also need to include the template file.

andy AT andypope DOT info

Cheers
Andy

 

[Al]

Andy,

I found your code helpful, and am trying to modify it to match my data
needs. Similar to the original question, I have many rows of data in one
worksheet that I would like to show on individual charts. However, I'd like
to show my data on pie-graphs. I've copied my code below, and it seems to
work with one problem. The charts show no data - they are all blank. Any
ideas?

Sub MakeCharts()

Dim rngData As Range
Dim rngHeader As Range
Dim rngDataRow As Range
Dim objChart As ChartObject
Dim sngTop As Single
Dim sngHeight As Single
Dim sngLeft As Single
Dim sngWidth As Single

Set rngHeader = Range("e3:e5")
Set rngData = Range("a2", Range("a2").End(xlDown)).Resize(, 3)

For Each rngDataRow In rngData.Rows
Set objChart = ActiveSheet.ChartObjects.Add(sngLeft, sngTop,
sngWidth, sngHeight)
With objChart.Chart
Charts.Add
ActiveChart.ChartType = xlPie
Do While .SeriesCollection.Count > 0
.SeriesCollection(1).Delete
Loop
With .SeriesCollection.NewSeries
.Name = rngDataRow.Cells(1).Value
.XValues = rngHeader
.Values = rngDataRow
End With

End With
sngTop = sngTop + sngHeight
Next

End Sub



Thank you,
Al