Want to copy Excel Chart to another sheet without the Data

[LuniA]

I have got 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 as well. 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.

[Koman]

Here is a way that makes a new duplicate sheet. Open the two workbooks and in XL 2003 use Windows > Arrange or in Xl 2007 use View > Arrange to have them side by side. If you are talking about one workbook then just click on the tab of the worksheet holding the data and charts and 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.

[QUENBY]

Is the chart embedded in the sheet? Or is it a standalone chart sheet. If its 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.

[GALIENA]

You have 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 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 is suggested 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.

[DanielaA]

I just found a solution to this and I've been wrestling with this problem for months now, so I wanted to share it:

• Create a New Workbook
• Copy the worksheet to the new workbook (right click: Move or Copy, select create a copy and put it in the new workbook)
• Change the name of the worksheet to its new name in the new workbook
• Move the newly named worksheet back to the old workbook.

The chart references will point to the data on the newly named worksheet.

[Bontu]

I was reading this today as I was having the same problem. However, I have found that if I use the "Links" option under the edit menu, and change the source of the offending link to the new workbook containing the copied data and charts, all the links are changed properly.