Identifying copied charts on a worksheet

  • Thread starter ragtopcaddy via
  • Start date


ragtopcaddy via

I have a template.xls file that contains one "form" (actually a range, A1:
AE28, formatted for my data) for displaying store data. This "form" includes
8 small charts that have datasources on the same range as the charts. I can
easily determine the datasource range for each of the 8 charts, but am having
difficulty assigning the datasource to the correct chart due to the
difficulty in identifying, from my code, which chart goes with which

This is only the beginning of the problem. What my Access code does, is it
opens the "template.xls" and saves it as "current.xls". Then I copy the
"form", including the charts, and paste it below the original. I then
populate the original, at the top of the worksheet, with the store data. Then
I copy the empty copy below the form I've just populated, paste it again
under the empty form I just copied, and proceed to populate the second form.
Then I copy the 3rd form and paste it underneath again, and so on, and so on,
until all 900+ stores have forms, each containing 8 small charts, with source
data in ranges to their right.

I hope this makes sense to someone who can give me some guidance. I've
learned that the sourcedata is a property of the chart, which is a property
of the chartobject in the chartobjects collection of the worksheet, but have
not been able to concisely identify the location of each chart so I can
supply the corresponding source data ranges. As it is, when I copy the charts,

they retain their original source data at the top of the worksheet. I need to
change that to reflect the ranges to the right of the charts as I copy and
paste them in the worksheet.

Here's a code sample I tried to use to rename the chartobjects, but I don't
know if the chartobjects are listed in the collection in the same order they
appear on the worksheet:

Function RenameCharts(x As Integer) As String
'renames charts to reflect actual # in worksheet
Dim CO As ChartObject
Dim n As Integer

For Each CO In Sheets("StoreReports").ChartObjects
n = n + 1
CO.Name = "ChObj " & n
Next CO

End Function

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Message posted via



Jon Peltier

Could you use something like this, collect the positions as distances from
the top of the sheet, and then process the charts in order of their

For i = 1 to ActiveSheet.ChartObjects.Count
Distance(i) = ActiveSheet.ChartObjects(i).Top

- Jon

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question