Identifying copied charts on a worksheet

  • Thread starter ragtopcaddy via OfficeKB.com
  • Start date
R

ragtopcaddy via OfficeKB.com

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
datasource.

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 OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-chart/200802/1
 
J

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
position:

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

- 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

Top