Dynamic sourcedata when copying chart within sheet

[Josip.2000]

Hi!

I'm working on a document that needs to allow the user to add more
"rows" (a "row" contains a list of numbers and a chart depicting these
numbers).

The problem however is that when copy-pasting new "rows", the charts
in the new "row" point to the numbers in the original "row" above.

Is there a a way to make this operation userfriendly? A macro maybe?
I tried to make some kind of macro but due to my limited skills I
don't seem to get it right. For example, how do I select a specific
chart from the new row (a row can contain up to 10 charts) when I
don't know it's name? (Example: ActiveSheet.ChartObjects("Chart
7").Activate --- does not work, since Chart 7 isn't dynamic...)

Any other solutions?
BR,
Josip

 

[Jon Peltier]

Make a template that has one chart and one chart's bit of data. Save and
close the template. To add a new chart linked to a new data range:

1. Create a new file based on the template
2. Cut (not copy) the range containing the chart and data
3. Paste into the sheet with multiple charts. This pasted chart will link to
the pasted data in the pasted cells.
4. Replace the pasted data with the data you need your new chart to plot.
5. Close the new workbook you created.

Repeat as necessary.

- Jon

 

[Josip.2000]

Make a template that has one chart and one chart's bit of data. Save and
close the template. To add a new chart linked to a new data range:

1. Create a new file based on the template
2. Cut (not copy) the range containing the chart and data
3. Paste into the sheet with multiple charts. This pasted chart will link to
the pasted data in the pasted cells.
4. Replace the pasted data with the data you need your new chart to plot.
5. Close the new workbook you created.

Repeat as necessary.

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

Ah, cut... Excel works in mysterous ways.
Need to work out some type of macro for this then

 

[Jon Peltier]

Yes, Cut. If you Copy, the chart source doesn't change, it's still in the
copied location, and the chart isn't forced to update. It's the same with a
whole sheet. Copying it doesn't work, but moving it does.

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