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.

"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.
A= Serial number
B,C,D= Data

Thanks for your reply.

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.
A= Serial number
B,C,D= Data

Thanks for your reply.

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\T emplates\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.
A= Serial number
B,C,D= Data

Thanks for your reply.

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" 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\Microsoft\T emplates\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.
A= Serial number
B,C,D= Data

Thanks for your reply.

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.crtx" )

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\Microsoft\T emplates\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.
A= Serial number
B,C,D= Data

Thanks for your reply.

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.crtx" )

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\Microsoft\T emplates\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.
A= Serial number
B,C,D= Data

Thanks for your reply.

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.
A= Serial number
B,C,D= Data

Thanks for your reply.





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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.

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Al" wrote in message
...
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.

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



"Andy Pope" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Al" wrote in message
...
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

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"aliao" wrote in message
...
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



"Andy Pope" wrote:

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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Al" wrote in message
...
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