multiple charts

  • Thread starter Thread starter MBarna
  • Start date Start date
M

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.
 
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.
 
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.
 
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
 
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
 
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
 
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
 
Hi,

This works for me.
I think you problem with the code you posted was that you were not
setting a size for the added chartobjects. This meant the chart were
being added to top left corner with no height or width.
So in order to get charts you added the Charts.Add command, which
created chart sheets. But the code was using the With command to
reference the chartobject on the worksheet rather than the recently
added chart sheet.
This will create chart objects on the worksheet.

'------------------
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)

' chart dimension and start position
sngLeft = rngData.Left + rngData.Width
sngWidth = rngData.Width * 2
sngTop = rngData.Top
sngHeight = sngWidth * 0.45

For Each rngDataRow In rngData.Rows
Set objChart = ActiveSheet.ChartObjects.Add(sngLeft, _
sngTop, sngWidth, sngHeight)
With objChart.Chart
.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
'--------------------------

You might want to check your worksheet for hidden chart objects.
Use Goto dialog to find Objects. CTRL+G, Special... > Objects.

Cheers
Andy
 
Andy,

Thanks for your response - your code worked, and I was able to get what I
needed. Another question though, just for my own education - I was
originally trying to get each chart on it's own tab. The code I originally
sent you created a new tab for each row of data, and there was a chart on
each tab, but the charts were not pulling the right data (the chart format
was right, but the source data was blank). Do you know what was missing from
the code? I tried a few things, and was able to determine that if I
highlighted all my original source data (all rows), I would get all the
charts to pull all the data (45 rows, 3 columns worth), and if no cells were
highlighted, the charts would have no data.

Any thoughts would be helpful, although the code you sent me did get me
charts that are easier to deal with than what I was originally trying to do.

Thanks,
Al
 
When you add a chart sheet Excel attempts to guess your data layout.
If the activecell is empty with no data around it the chart will be empty.
But if the active cell is in the middle of a data set all the data will be
used.

To get the chart to contain the correct data you could, although I wouldn't,
select the cells before using Charts.Add
I would either use the SetSource method of the newly created chart or as in
this code empty the chart of data before populating series.

Cheers
Andy
 
Hi Andy,

I am working on a similar problem trying to use this macro to create
multiple line charts. (In my case, 10 charts for 10 series data) The codes
from this post are working but need some modifications for my purpose. Could
you please help me with my codes to put each chart into different worksheet
and maybe change the tab name of the worksheet to the series name in my data?

Also I was able to add the title for the chart and X, Y axises but not sure
how to also insert the series name into my chart title and move the Y axis to
buttom of the chart.... I am not a heavy excel user.....

Here are my codes:

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("Y9:AE9")
Set rngData = Range("Y10", Range("Y10").End(xlDown)).Resize(, 7)

' chart dimension and start position
sngLeft = rngData.Left + rngData.Width
sngWidth = rngData.Width * 2
sngTop = rngData.Top
sngHeight = sngWidth * 0.45

For Each rngDataRow In rngData.Rows
Set objChart = ActiveSheet.ChartObjects.Add(sngLeft, sngTop,
sngWidth, sngHeight)
With objChart.Chart
.ChartType = xlLine
Do While .SeriesCollection.Count > 0
.SeriesCollection(1).Delete
Loop
With .SeriesCollection.NewSeries
.Name = rngDataRow.Offset(0, -1).Cells(1).Value
.XValues = rngHeader
.Values = rngDataRow
End With
.HasTitle = True
.ChartTitle.Characters.Text = "Total Weight Loss for Subject"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Elapsed Time (Weeks)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total
Weight Loss"
End With
sngTop = sngTop + sngHeight
Next

End Sub

Thank you in advance,
Aurora
 
Hi,

This revision will add new worksheet and create a chartobject.
The code is already there for adding titles. You just need to change the
text used or cells referenced for text.

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
Dim shtNew As Worksheet
Dim shtData As Worksheet

Set shtData = ActiveSheet
Set rngHeader = shtData.Range("Y9:AH9")
Set rngData = shtData.Range("Y10",
shtData.Range("Y10").End(xlDown)).Resize(, 10)

' chart dimension and start position
sngLeft = rngData.Left + rngData.Width
sngWidth = rngData.Width
sngTop = rngData.Top
sngHeight = sngWidth * 0.45

For Each rngDataRow In rngData.Rows
Set shtNew = Worksheets.Add(After:=Worksheets(Worksheets.Count))
shtNew.Name = rngDataRow.Offset(0, -1).Cells(1).Value

Set objChart = shtNew.ChartObjects.Add(sngLeft, sngTop, sngWidth,
sngHeight)
With objChart.Chart
.ChartType = xlLine
Do While .SeriesCollection.Count > 0
.SeriesCollection(1).Delete
Loop
With .SeriesCollection.NewSeries
.Name = rngDataRow.Offset(0, -1).Cells(1).Value
.XValues = rngHeader
.Values = rngDataRow
End With
.HasTitle = True
.ChartTitle.Characters.Text = "Total Weight Loss for
Subject"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Elapsed Time (Weeks)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total
Weight Loss"
End With
sngTop = sngTop + sngHeight
Next

End Sub

Cheers
Andy
 
Back
Top