I am having a problem editing an excel template which I managed to cobble
together a while back.

The template is a dashboard report. It contains one specimen of a dashboard
for one store.

The way it works is that for each store being reported on, the range
containing the specimen is copied and pasted. At the end of that proccess
(maybe hundreds of dashboards), 8 chart objects in each dashboard are
connected to their source data:

i = 9
x = conRows '33
On Error GoTo OuttaHere
Do
'Link the charts to their source data:
For y = 0 To 7
shtRpt.ChartObjects(i + y).Activate
XLobj.ActiveChart.SetSourceData _
Source:=XLobj.Sheets("StoreReports").Range(shtRpt. Cells(x + (2 *
y), conCols - 5), _
shtRpt.Cells(x + 1 + (2 * y), conCols)), PlotBy:=xlRows
Next y
i = i + 8
x = x + conRows
Loop

I've been running this code for months and it works like a charm, although I
had to wrestle the template to the ground and beat it into submission,
rearranging the charts until they were in the order that Excel insisted they
be. Before I rearranged them, they were all out of position with regard to
their source data.

OK, so this works fine. But now, I'm required to add 2 more charts. So I
copied and pasted the bottom 2 of the 8 charts below themselves and now have
10 charts. I changed the above code to:

For y = 0 To 9

Unfortunately, the range Excel selects for my 2 new charts using the above
loop was not the data that had been added below the ranges for the other 8
charts (8 2-row data ranges, neatly listed in a 16 row by 5 column range, now
increased to 10 2-row data ranges neatly listed in a 20 row by 5 column range
to the right of the dashboard, out of the print area). Instead of linking to
those 2 new sources (R17C26:R18C31) and (R19C26:R20C31), my new graphs linked
instead to the empty ranges (R35C26:R36C31) and (R33C26:R34C31), which are
not only empty and below the dashboard's range (such that these 2 graphs for
this store would actually find themselves linked to data from the next
store's dashboard), but the order is reversed. The graph on the left should
be linked to the higher positioned data, R33, not R35, if the code was
cycling through the graphs in order. I can find no way to accurately identify
or rename the graphs. I only hit on the successful 8 graph solution after
days of trial and error (I have no idea why it works!).

I have a function that returns the names of the graphs in my immediate window:


Sub ChartNames()
Dim CO As ChartObject
Dim n As Integer

For Each CO In Sheets("Template").ChartObjects
Debug.Print CO.Name
Next CO

End Sub

When I run it for my new template, I get the following list:

Chart 1
Chart 2
Chart 3
Chart 4
Chart 5
Chart 6
Chart 7
Chart 8
Chart 7
Chart 1

How is it that duplicate names are even possible?! This is really mind-
numbing!

Please help!

Thanks,

--
Bill Reed

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200805/1

Give the charts custom names
(http://peltiertech.com/Excel/ChartsH...meAChart.html), then place these
names in a range or a VBA array. Loop through the list of names so you know
that you are choosing the chart you intend. Actually to make looping easier,
you could use names like BillChart01, BillChart02.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ragtopcaddy via OfficeKB.com" <u9289@uwe wrote in message
news:8482facd6d06b@uwe...
I am having a problem editing an excel template which I managed to cobble
together a while back.

The template is a dashboard report. It contains one specimen of a
dashboard
for one store.

The way it works is that for each store being reported on, the range
containing the specimen is copied and pasted. At the end of that proccess
(maybe hundreds of dashboards), 8 chart objects in each dashboard are
connected to their source data:

i = 9
x = conRows '33
On Error GoTo OuttaHere
Do
'Link the charts to their source data:
For y = 0 To 7
shtRpt.ChartObjects(i + y).Activate
XLobj.ActiveChart.SetSourceData _
Source:=XLobj.Sheets("StoreReports").Range(shtRpt. Cells(x + (2
*
y), conCols - 5), _
shtRpt.Cells(x + 1 + (2 * y), conCols)), PlotBy:=xlRows
Next y
i = i + 8
x = x + conRows
Loop

I've been running this code for months and it works like a charm, although
I
had to wrestle the template to the ground and beat it into submission,
rearranging the charts until they were in the order that Excel insisted
they
be. Before I rearranged them, they were all out of position with regard to
their source data.

OK, so this works fine. But now, I'm required to add 2 more charts. So I
copied and pasted the bottom 2 of the 8 charts below themselves and now
have
10 charts. I changed the above code to:

For y = 0 To 9

Unfortunately, the range Excel selects for my 2 new charts using the above
loop was not the data that had been added below the ranges for the other 8
charts (8 2-row data ranges, neatly listed in a 16 row by 5 column range,
now
increased to 10 2-row data ranges neatly listed in a 20 row by 5 column
range
to the right of the dashboard, out of the print area). Instead of linking
to
those 2 new sources (R17C26:R18C31) and (R19C26:R20C31), my new graphs
linked
instead to the empty ranges (R35C26:R36C31) and (R33C26:R34C31), which are
not only empty and below the dashboard's range (such that these 2 graphs
for
this store would actually find themselves linked to data from the next
store's dashboard), but the order is reversed. The graph on the left
should
be linked to the higher positioned data, R33, not R35, if the code was
cycling through the graphs in order. I can find no way to accurately
identify
or rename the graphs. I only hit on the successful 8 graph solution after
days of trial and error (I have no idea why it works!).

I have a function that returns the names of the graphs in my immediate
window:


Sub ChartNames()
Dim CO As ChartObject
Dim n As Integer

For Each CO In Sheets("Template").ChartObjects
Debug.Print CO.Name
Next CO

End Sub

When I run it for my new template, I get the following list:

Chart 1
Chart 2
Chart 3
Chart 4
Chart 5
Chart 6
Chart 7
Chart 8
Chart 7
Chart 1

How is it that duplicate names are even possible?! This is really mind-
numbing!

Please help!

Thanks,

--
Bill Reed

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200805/1

When I originally attempted this several months ago. I tried to do what you
suggest here, but it proved to be impossible. I don't know why my current
method works, but it does (with some difficulty).

Thanks,



Jon Peltier wrote:
Give the charts custom names
(http://peltiertech.com/Excel/ChartsH...meAChart.html), then place these
names in a range or a VBA array. Loop through the list of names so you know
that you are choosing the chart you intend. Actually to make looping easier,
you could use names like BillChart01, BillChart02.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

I am having a problem editing an excel template which I managed to cobble
together a while back.
[quoted text clipped - 94 lines]

Thanks,

--
Bill Reed

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200805/1

Did you try renaming your charts? If you rely on default chart names, you
will never be sure what you have.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ragtopcaddy via OfficeKB.com" <u9289@uwe wrote in message
news:848a6056aabab@uwe...
When I originally attempted this several months ago. I tried to do what
you
suggest here, but it proved to be impossible. I don't know why my current
method works, but it does (with some difficulty).

Thanks,



Jon Peltier wrote:
Give the charts custom names
(http://peltiertech.com/Excel/ChartsH...meAChart.html), then place
these
names in a range or a VBA array. Loop through the list of names so you
know
that you are choosing the chart you intend. Actually to make looping
easier,
you could use names like BillChart01, BillChart02.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

I am having a problem editing an excel template which I managed to cobble
together a while back.
[quoted text clipped - 94 lines]

Thanks,

--
Bill Reed

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

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200805/1

I tried, but failed to rename them. I had some code I downloaded and tried to
rename the charts, but it had no effect on the outcome. I don't recall the
exact nature of the failure. The method I'm using, as mysterious as it is,
gets the job done, so I'm not revisiting that solution again.

Thanks for you help,

Bill

Jon Peltier wrote:
Did you try renaming your charts? If you rely on default chart names, you
will never be sure what you have.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

When I originally attempted this several months ago. I tried to do what
you
[quoted text clipped - 24 lines]

Thanks,

--
Bill Reed

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

Message posted via http://www.officekb.com

Sorry for the confusion!

The changes required to successfully connect the additional 2 charts to their
data sources are noted below:

'i = 9
i = 11
x = conRows ' 33
On Error GoTo OuttaHere
Do
'Link the charts to their source data:
For y = 0 To 7
' For y = 0 To 9
shtRpt.ChartObjects(i + y).Activate
XLobj.ActiveChart.SetSourceData _
Source:=XLobj.Sheets("StoreReports").Range(shtRpt. Cells(x + (2 *
y), conCols - 5), _
shtRpt.Cells(x + 1 + (2 * y), conCols)), PlotBy:=xlRows
Next y
' i = i + 8
i = i + 10
x = x + conRows
Loop

It was not just the "For y = 0 To 7" line that had to be increased by 2, but
"i = 9" and "i = i + 8" as well.

--
Bill Reed

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

Message posted via http://www.officekb.com