ChartObjects

[Guest]

I am writing a VB routine to copy a chart from 20 workbooks to a new single
worksheet in a new workbook. I will also update the charts in the new
workbook perodically. The following will be called 20 times with a different
Oneof20 and ChartLoc.

Sub CopyGraph (Oneof20 As String, ChartLoc As String)
'
' Open workbook
Workbooks.Open Filename:= Oneof20
Windows(EachFile).Activate
Sheets("XYZ").Select

'
'Copy new chart
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False
'
' paste new chart
Windows("ALL.xls").Activate
Sheets("20Charts").Select
Range(ChartLoc).Select
ActiveSheet.Paste
'
' change chart title
ActiveSheet.ChartObjects(ChartName).Activate
..
..
..

How can I determine what ChartName is? If I do the "copy the 20 charts" a
2nd time (say a month later), I will have to delete the original 20 charts
first. How do I get the names of those 20 charts so I can reference them to
delete?

 

[Guest]

Scott,

Your procedure is attempting to activate a chart that will already be
active. When you paste the chart it becomes the active item. So if you want
to get the name of the chart you just need to use ActiveChart.Name. You
could use a blank sheet to list the chart names

Sheet1.Range("A65536").End(xlUp).Offset(1, 0) = ActiveChart.Name

and then reference this list when you want to delete the charts

Sheets("20charts").Activate
For Each chartname In ActiveSheet.ChartObjects
Set rng = Sheet1.Range("A2", Range("A65536").End(xlUp))
MsgBox (rng.Address & " " & chartname.Name)
Set found = rng.Find(chartname.Name)
If Not found Is Nothing Then chartname.Delete
Next chartname

Where Sheet1 is the sheet you write the chartnames to.

Hope this helps you out.

Mike