If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
copy chart between multiple sheets in workbook
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 |
#2
|
|||
|
|||
copy chart between multiple sheets in workbook
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/ On 3/4/2010 4:15 PM, Sam wrote: 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 |
#3
|
|||
|
|||
copy chart between multiple sheets in workbook
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" wrote: 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/ On 3/4/2010 4:15 PM, Sam wrote: 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 . |
#4
|
|||
|
|||
copy chart between multiple sheets in workbook
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 he How to Edit Series Formulas » Peltier Tech Blog http://peltiertech.com/WordPress/how...ries-formulas/ - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 3/5/2010 12:28 PM, Sam wrote: 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" wrote: 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/ On 3/4/2010 4:15 PM, Sam wrote: 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 . |
#5
|
|||
|
|||
copy chart between multiple sheets in workbook
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 On 04/03/2010 21:15, Sam wrote: 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 |
Thread Tools | |
Display Modes | |
|
|