copying chart

I am using Excel to automatically generate charts. The charts are located on
their own page.
What I want to accomplish next is to copy the chart and paste on another
sheet with some other stuff. I want to copy the entire chart inluding legend
and titles and such. However it appears that each time I run the program for
different data sets, the generated charts have different names.
I thought I would add a step to the generation so that the chart is named
where then I could then reference the chart by name to copy it. However, I
am not able to get the naming syntax down correctly.
Any help would be greatly appreciated.
TIA

Hi,

You can change the name of a chartobject with the following,

Activechart.Parent.name = "MyName"

Cheers
Andy

Papa Jonah wrote:
> I am using Excel to automatically generate charts. The charts are located on
> their own page.
> What I want to accomplish next is to copy the chart and paste on another
> sheet with some other stuff. I want to copy the entire chart inluding legend
> and titles and such. However it appears that each time I run the program for
> different data sets, the generated charts have different names.
> I thought I would add a step to the generation so that the chart is named
> where then I could then reference the chart by name to copy it. However, I
> am not able to get the naming syntax down correctly.
> Any help would be greatly appreciated.
> TIA
>

--

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

Andy,
That didn't work. It just caused the code to break. I tried adding a line
above it that read:
activechart.parent.select
but that did not help.
What might I be doing wrong?

"Andy Pope" wrote:

> Hi,
>
> You can change the name of a chartobject with the following,
>
> Activechart.Parent.name = "MyName"
>
> Cheers
> Andy
>
> Papa Jonah wrote:
> > I am using Excel to automatically generate charts. The charts are located on
> > their own page.
> > What I want to accomplish next is to copy the chart and paste on another
> > sheet with some other stuff. I want to copy the entire chart inluding legend
> > and titles and such. However it appears that each time I run the program for
> > different data sets, the generated charts have different names.
> > I thought I would add a step to the generation so that the chart is named
> > where then I could then reference the chart by name to copy it. However, I
> > am not able to get the naming syntax down correctly.
> > Any help would be greatly appreciated.
> > TIA
> >
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>

Are you running a macro to make the chart?

yes

"Chip" wrote:

> Are you running a macro to make the chart?
>
>

Give me your code for that, the problem with Andy Pope's code is that
it needs to be modded for your code a little bit (i.e. you may need to
drop the activechart part)

Chip, Its alot. There must be a more efficient way to do this, but I think
it is easy to figure out what I am trying to accomplish.
Thanks for looking at it.

Sub MakeChart()

'the following line needs to subtract the number of rows that have
calculations at the bottom of set
Sheets("Data (altered)").Select

Set myrange = Range("a3").CurrentRegion.Resize(numberrows)

Range("A3:E" & numberrows & ",H3:H" & numberrows).Select
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="LANL PI"
ActiveChart.SetSourceData Source:=Sheets("Data (altered)").Range( _
"A3:E" & numberrows & ",H3:H" & numberrows), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Performance Index (PI)"""
ActiveChart.SeriesCollection(2).Name = "=""Trend"""
ActiveChart.SeriesCollection(3).Name = "=""Upper Control Limit"""
ActiveChart.SeriesCollection(4).Name = "=""Lower Control Limit"""
ActiveChart.SeriesCollection(5).Name = "='Data (altered)'!R21C6"

With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _
"Performance Index" & Chr(10) & "Larger Numbers are Better"
.Axes(xlCategory).MaximumScale = endchart '38270
.Axes(xlCategory).MinimumScale = 35339
.Axes(xlCategory).MinorUnit = 366
.Axes(xlCategory).MajorUnit = 366
.Axes(xlCategory).Crosses = xlCustom
.Axes(xlCategory).CrossesAt = 0
.Axes(xlCategory).ScaleType = xlLinear
.Axes(xlCategory).DisplayUnit = xlNone
End With


ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlCustom
.CrossesAt = 0
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'ActiveChart.Location Where:=xlLocationAsObject, Name:="chart"
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Control Chart"
Selection.ShapeRange.ScaleWidth 0.66, msoFalse, msoScaleFromTopLeft
'ActiveChart.Parent.Select
'ActiveChart.Parent.Name = "MyName"



"Chip" wrote:

> Give me your code for that, the problem with Andy Pope's code is that
> it needs to be modded for your code a little bit (i.e. you may need to
> drop the activechart part)
>
>

Add this after Charts.Add

With .Chart
..Parent.Name = "My Chart"
End With

If that doesnt work i would expect you can add
..Parent.Name="My Chart"

to the With ActiveChart section

Hi,

This code worked for me and can be placed at the end of you code example.

activechart.Location xlLocationAsObject , "Sheet2"
activechart.Parent.name = "MyName"

Cheers
Andy

Papa Jonah wrote:
> Andy,
> That didn't work. It just caused the code to break. I tried adding a line
> above it that read:
> activechart.parent.select
> but that did not help.
> What might I be doing wrong?
>
> "Andy Pope" wrote:
>
>
>>Hi,
>>
>>You can change the name of a chartobject with the following,
>>
>>Activechart.Parent.name = "MyName"
>>
>>Cheers
>>Andy
>>
>>Papa Jonah wrote:
>>
>>>I am using Excel to automatically generate charts. The charts are located on
>>>their own page.
>>>What I want to accomplish next is to copy the chart and paste on another
>>>sheet with some other stuff. I want to copy the entire chart inluding legend
>>>and titles and such. However it appears that each time I run the program for
>>>different data sets, the generated charts have different names.
>>>I thought I would add a step to the generation so that the chart is named
>>>where then I could then reference the chart by name to copy it. However, I
>>>am not able to get the naming syntax down correctly.
>>>Any help would be greatly appreciated.
>>>TIA
>>>
>>
>>--
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info
>>

--

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

Chip, That doesn't seem to work either. I tried changing the location of the
entry and even tried playing with the syntax.
I'm totally at a loss.

"Chip" wrote:

> Add this after Charts.Add
>
> With .Chart
> ..Parent.Name = "My Chart"
> End With
>
> If that doesnt work i would expect you can add
> ..Parent.Name="My Chart"
>
> to the With ActiveChart section
>
>