copy chart between multiple sheets in workbook

[Sam]

Hi,

I have workbook with about 50 sheets. they have exactly the same table ,
just data(numbers in the table) are different. Also created a chart
based on the table in the first worksheet. Chart is embedded in the
sheet). I spend some time to format the chart and want to copy and paste
it on the other sheets, but I want to reflect the data in each sheet. So
each sheet has its own graph, which looks similar but reflects data on
the sheet.
Problem I am having is when I copy/paste it it paste absolute reference
, so I have manually to fix data range and with that many sheets its
kinda cumbersome.. and this is not the only file
Any suggestions how I can copy my chart so it reflect the data on the
sheet that is copied to?

Thanks,
Sam

 

[Jon Peltier]

The easiest and least tedious way is to copy the original worksheet that
contains the chart. The copied chart thus links to the copied worksheet.
Now copy the data from the table on the second worksheet, and
paste-special-values or -formulas into the table on the copied worksheet.

Lather, rinse, repeat.

- Jon

 

[Sam]

Still its tons of copy paste... i was trying to use
MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
to get the sheet name and then indirect formula to "glue" it to the range,
but when i put that in the series formula, it tells me its not valid
Is there any other way? Like Can it be done with VBA Code, like telling it
to loop through all sheets, and construct a graph based on sheets name and
specific range? (note the data range s same on every sheet - lets say axes
names are in A1:A100, and data is in B1:B100)I need simple line graph to
chart the trend and then apply a custom style i saved as template (working on
2007 or 2010beta)

Any suggestions will be appreciated

 

[Jon Peltier]

You could paste the chart onto a different sheet, even though it links
to the original sheet. Then you can change the sheet name in the series
formulas.

Normally this is a pain to do, but I wrote a little utility that handles
it. I didn't mention it at first because sometimes Excel is funny about
editing sheet names in the series formula. But if your sheet names are
simple, it should work okay. Read about the utility here:

How to Edit Series Formulas » Peltier Tech Blog
http://peltiertech.com/WordPress/how-to-edit-series-formulas/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/

Still its tons of copy paste... i was trying to use
MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
to get the sheet name and then indirect formula to "glue" it to the range,
but when i put that in the series formula, it tells me its not valid
Is there any other way? Like Can it be done with VBA Code, like telling it
to loop through all sheets, and construct a graph based on sheets name and
specific range? (note the data range s same on every sheet - lets say axes
names are in A1:A100, and data is in B1:B100)I need simple line graph to
chart the trend and then apply a custom style i saved as template (working on
2007 or 2010beta)

Any suggestions will be appreciated

The easiest and least tedious way is to copy the original worksheet that
contains the chart. The copied chart thus links to the copied worksheet.
Now copy the data from the table on the second worksheet, and
paste-special-values or -formulas into the table on the copied worksheet.

Lather, rinse, repeat.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
774-275-0064
http://peltiertech.com/



.

 

[AdamV]

Thinking outside the box:
if your data is basically the same layout, same number of series and
categories etc, then select your chart, go to "save as template" and
give it a sensible name.
Now on each sheet you need to select your data, insert a chart and
choose your template as the chart type.

You could even macro record the insert job, andy postioning and resizing
etc, then repeat this on every sheet.

Hope this helps (even though it does not actually copy your chart at all!)

Adam