Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to copy sheet with charts without link to original data
I have a worksheet with ten charts on it. I need to copy the worksheet with
all its data and related charts to new sheets or files so I have a page for every day, with different data entered every day. But when I copy the worksheet with embedded charts and paste it into a new file, the charts pull their source data from the original worksheet, not the new one. The source data Value box is written as such, ='03-09-10'!$D$12 any ideas? |
#2
|
|||
|
|||
Answer: how to copy sheet with charts without link to original data
Here's how you can copy a worksheet with charts without linking to the original data:
That's it! You now have a copy of the worksheet with all its data and charts, without any links to the original data. Let me know if you have any questions or need further assistance.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to copy sheet with charts without link to original data
Hi Esty, Probably wont help you, but I had similar problem, I posted few days ago. Didn't resolved it yet, but Jon suggested a tool on his website. Doesn't do all the job, but still half way there - allows you to automate the renaming. Didnt tried it my self - I did it the hard way renaming 3 sheets 50 tabs each (gosh there were 150 graphs :( ), - but if it works it will save you some time that was my thread: http://www.microsoft.com/communities...sloc=en-us&p=1 the link to the tool: http://peltiertech.com/WordPress/how...ries-formulas/ Not sure how helpful that is, but it is at least an option. If you find any better (automated method) of renaming the series of the chart, let us know. I'll appreciate it. Sam On 3/10/2010 11:15 AM, Esty wrote: I have a worksheet with ten charts on it. I need to copy the worksheet with all its data and related charts to new sheets or files so I have a page for every day, with different data entered every day. But when I copy the worksheet with embedded charts and paste it into a new file, the charts pull their source data from the original worksheet, not the new one. The source data Value box is written as such, ='03-09-10'!$D$12 any ideas? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to copy sheet with charts without link to original data
Are you copying by Copy and Paste?
Here is a way that makes a new duplicate sheet Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007 use View |Arrange to have them side by side If you are talking about one workbook just follow from here Click on the tab of the worksheet holding the data and charts Hold down the CTRL key and drag the tab from one book to the other (you will see an icon looking like a piece of paper with a + sign) --- with one file drag from one place to another in the tab line-up best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Esty" wrote in message ... I have a worksheet with ten charts on it. I need to copy the worksheet with all its data and related charts to new sheets or files so I have a page for every day, with different data entered every day. But when I copy the worksheet with embedded charts and paste it into a new file, the charts pull their source data from the original worksheet, not the new one. The source data Value box is written as such, ='03-09-10'!$D$12 any ideas? |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to copy sheet with charts without link to original data
Just tried your suggestion. Didn't work for me. Copies the sheet with the
data, but not the chart. Or may be I am doing sth wrong? I followed you steps in 2007, tried in 1 workbook dragging Sheet1 to the end - creates copy with the data-no raph Tried between 2 files - same result - copied sheet with data on it, but didnt transfer the graph... Any Excel settings I am missing? Is it possible this to be done with VBA, as instead "Sheet1" there is dynamic reference to the sheets name in ..Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14") something like the formula =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) My VBA knowledge is limited, and I cant make this to work so far... Any suggestions will be appreciated Thanks Sam "Bernard Liengme" wrote: Are you copying by Copy and Paste? Here is a way that makes a new duplicate sheet Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007 use View |Arrange to have them side by side If you are talking about one workbook just follow from here Click on the tab of the worksheet holding the data and charts Hold down the CTRL key and drag the tab from one book to the other (you will see an icon looking like a piece of paper with a + sign) --- with one file drag from one place to another in the tab line-up best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Esty" wrote in message ... I have a worksheet with ten charts on it. I need to copy the worksheet with all its data and related charts to new sheets or files so I have a page for every day, with different data entered every day. But when I copy the worksheet with embedded charts and paste it into a new file, the charts pull their source data from the original worksheet, not the new one. The source data Value box is written as such, ='03-09-10'!$D$12 any ideas? . |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to copy sheet with charts without link to original data
Sorry, I cannot see anyway to solve your problem
I have just repeated the exercise and it really does work My chart is non SHeet2 and when I click the data series in the chart, the formula bar displays =SERIES(,Sheet2!$A$7:$A$11,Sheet2!$B$7:$B$11,1) After by 'copy by dragging tab', on Sheet2 (2) I see =SERIES(,Sheet2 (2)!$A$7:$A$11,Sheet2!$B$7:$B$11,1) So the reference to the sheet does no seem to be holding it up I cannot see anything in Options that would foul us up Want to send me a sample file ? Get my email addy from my website best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Sam" wrote in message ... Just tried your suggestion. Didn't work for me. Copies the sheet with the data, but not the chart. Or may be I am doing sth wrong? I followed you steps in 2007, tried in 1 workbook dragging Sheet1 to the end - creates copy with the data-no raph Tried between 2 files - same result - copied sheet with data on it, but didnt transfer the graph... Any Excel settings I am missing? Is it possible this to be done with VBA, as instead "Sheet1" there is dynamic reference to the sheets name in .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14") something like the formula =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) My VBA knowledge is limited, and I cant make this to work so far... Any suggestions will be appreciated Thanks Sam "Bernard Liengme" wrote: Are you copying by Copy and Paste? Here is a way that makes a new duplicate sheet Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007 use View |Arrange to have them side by side If you are talking about one workbook just follow from here Click on the tab of the worksheet holding the data and charts Hold down the CTRL key and drag the tab from one book to the other (you will see an icon looking like a piece of paper with a + sign) --- with one file drag from one place to another in the tab line-up best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Esty" wrote in message ... I have a worksheet with ten charts on it. I need to copy the worksheet with all its data and related charts to new sheets or files so I have a page for every day, with different data entered every day. But when I copy the worksheet with embedded charts and paste it into a new file, the charts pull their source data from the original worksheet, not the new one. The source data Value box is written as such, ='03-09-10'!$D$12 any ideas? . |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to copy sheet with charts without link to original data
Is the chart embedded in the sheet? Or is it a standalone chart sheet?
If it's a chart sheet, then select the data worksheet, hold Ctrl and select the chart sheet, then hold Ctrl and drag both to the other workbook. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 3/10/2010 5:01 PM, Sam wrote: Just tried your suggestion. Didn't work for me. Copies the sheet with the data, but not the chart. Or may be I am doing sth wrong? I followed you steps in 2007, tried in 1 workbook dragging Sheet1 to the end - creates copy with the data-no raph Tried between 2 files - same result - copied sheet with data on it, but didnt transfer the graph... Any Excel settings I am missing? Is it possible this to be done with VBA, as instead "Sheet1" there is dynamic reference to the sheets name in .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14") something like the formula =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) My VBA knowledge is limited, and I cant make this to work so far... Any suggestions will be appreciated Thanks Sam "Bernard Liengme" wrote: Are you copying by Copy and Paste? Here is a way that makes a new duplicate sheet Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007 use View |Arrange to have them side by side If you are talking about one workbook just follow from here Click on the tab of the worksheet holding the data and charts Hold down the CTRL key and drag the tab from one book to the other (you will see an icon looking like a piece of paper with a + sign) --- with one file drag from one place to another in the tab line-up best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme wrote in message ... I have a worksheet with ten charts on it. I need to copy the worksheet with all its data and related charts to new sheets or files so I have a page for every day, with different data entered every day. But when I copy the worksheet with embedded charts and paste it into a new file, the charts pull their source data from the original worksheet, not the new one. The source data Value box is written as such, ='03-09-10'!$D$12 any ideas? . |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to copy sheet with charts without link to original data
Sam
You need to enable an option for this to work if the charts are embedded on the worksheets where the data is, rather than on a chart sheet of their own. Go to Excel options Advanced Cut, Copy and Paste section. Enable "Cut, copy, and sort inserted objects with their parent cells" Now use Bernards Ctrl-drag technique, or a normal right click "Move or copy" to copy the sheet and the chart should update the formulas to the worksheet it is embedded on (not back to the original). If your charts are on worksheets other than the ones where their source data is, then you need to do something similar to what Jon suggests for chart sheets, ie to select the sheet with the chart on it and the sheet with the data on it and copy them all at once. The new copied sheet with the chart will then point at the new copied sheet with the data, rather than the old one. Hope this combination gets you sorted out. Adam On 10/03/2010 22:01, Sam wrote: Just tried your suggestion. Didn't work for me. Copies the sheet with the data, but not the chart. Or may be I am doing sth wrong? I followed you steps in 2007, tried in 1 workbook dragging Sheet1 to the end - creates copy with the data-no raph Tried between 2 files - same result - copied sheet with data on it, but didnt transfer the graph... Any Excel settings I am missing? Is it possible this to be done with VBA, as instead "Sheet1" there is dynamic reference to the sheets name in .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14") something like the formula =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) My VBA knowledge is limited, and I cant make this to work so far... Any suggestions will be appreciated Thanks Sam "Bernard Liengme" wrote: Are you copying by Copy and Paste? Here is a way that makes a new duplicate sheet Open the two workbooks and in XL2003 use Windows | Arrange or in Xl2007 use View |Arrange to have them side by side If you are talking about one workbook just follow from here Click on the tab of the worksheet holding the data and charts Hold down the CTRL key and drag the tab from one book to the other (you will see an icon looking like a piece of paper with a + sign) --- with one file drag from one place to another in the tab line-up best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme wrote in message ... I have a worksheet with ten charts on it. I need to copy the worksheet with all its data and related charts to new sheets or files so I have a page for every day, with different data entered every day. But when I copy the worksheet with embedded charts and paste it into a new file, the charts pull their source data from the original worksheet, not the new one. The source data Value box is written as such, ='03-09-10'!$D$12 any ideas? . |
#9
|
|||
|
|||
I know this is an old thread, but I just found a solution to this and I've been wrestling with this problem for months now so I wanted to share it:
1.Create a New Workbook 2. Copy the worksheet to the new workbook (right click: Move or Copy, select create a copy and put it in the new workbook) 3. Change the name of the worksheet to its new name in the new workbook 4. Move the newly named worksheet back to the old workbook The chart references will point to the data on the newly named worksheet. |
#10
|
|||
|
|||
Quote:
You need to enable an option for this to work if the charts are embedded on the worksheets where the data is, rather than on a chart sheet of their own. Go to Excel options Advanced Cut, Copy and Paste section. Enable "Cut, copy, and sort inserted objects with their parent cells" |
#11
|
|||
|
|||
Quote:
Regards Ian (using Excel 2003) |
#12
|
|||
|
|||
Quote:
Been wondering what had happened, with my copying of sheets with graphs, all of a sudden didn't work, i went and played with Advaned settings didn't i, but now fixed again all copies over graph and Macro buttons that work. Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Sheet to new Sheet and clear cells on original sheets | Excel Discussion (Misc queries) | |||
???Replicate a sheet but chart still link to original sheet | Charts and Charting in Excel | |||
How do I copy a worksheet without the link to the original workboo | Excel Discussion (Misc queries) | |||
Copy tabs(sheets) from workbook without link to original source | Excel Discussion (Misc queries) | |||
Copy tabs(sheets) from workbook without link to original source | Links and Linking in Excel |