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
|
|||
|
|||
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
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? . |
#5
|
|||
|
|||
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? . |
#6
|
|||
|
|||
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? . |
#7
|
|||
|
|||
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? . |
Thread Tools | |
Display Modes | |
|
|