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
"Andy Pope" wrote:
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
Al wrote:
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
"Andy Pope" wrote:
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 Pope, Microsoft MVP - Excel
http://www.andypope.info
"MBarna" wrote in message
...
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.cr tx")
Thank you in advance for your answer.
Cheers,
MBarna
"Andy Pope" wrote:
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\Microso ft\Templates\Charts\Chart1.crtx")
Cheers
Andy
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"MBarna" wrote in message
...
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" wrote:
Hi,
This dummy data in range A1:D5. Use text to columns to split it out.
Serial, A, B, C
001,10,6,4
002,7,5,6
003,4,8,6
004,3,6,7
This code in a standard code module. Will create a column of charts.
'----------------------
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("B1:D1")
Set rngData = Range("B2", Range("B2").End(xlDown)).Resize(, 4)
' 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
Do While .SeriesCollection.Count 0
.SeriesCollection(1).Remove
Loop
With .SeriesCollection.NewSeries
.Name = rngDataRow.Offset(0, -1).Cells(1).Value
.XValues = rngHeader
.Values = rngDataRow
End With
If .HasLegend Then .Legend.Delete
End With
sngTop = sngTop + sngHeight
Next
End Sub
'-------------------------
Cheers
Andy
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"MBarna" wrote in message
...
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.
"Andy Pope" wrote:
Hi,
The only way to do this in 1 shot is with code.
If you have xl2003 or older you can create the first chart whilst
recording
your actions.
Then modify the code to process the remaining 99 rows of data.
Jon Peltier has information on macros and charting
http://peltiertech.com/WordPress/200...our-own-macro/
http://peltiertech.com/Excel/ChartsH...kChartVBA.html
Cheers
Andy
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"MBarna" wrote in message
...
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.