copying chart

[Guest]

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]

Hi,

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

Activechart.Parent.name = "MyName"

Cheers
Andy

 

[Guest]

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?

 

[Chip]

Are you running a macro to make the chart?

 

[Guest]

yes

 

[Chip]

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)

 

[Guest]

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]

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

 

[Andy Pope]

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

 

[Guest]

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.