Chart references when copying worksheets

[Paul R]

I want to copy a worksheet which includes several charts. When I do this,
the charts refer to data on the original worksheet, as opposed to the new
worksheet.

How can I make a worksheet copy that has all its charts refer to cells on
the new sheet? Manually changing all the references would be very time
consuming, particularly if I want to make several copies of the sheet.

 

[Bernard Liengme]

Are you making the copy using Copy & Paste?
Try this instead: Hold down the CTRL key and drag the tab of the worksheet
you want to copy. Drag it left of right and when you release the mouse
button you will see a new sheet with a name like Sheet1 (2). This will be an
exact replica of the original and the chart will refer to cell on its on
sheet.
best wishes

 

[Simon]

In Excel 2007, when I use the CTRL key to drag a sheet with a graph that
contains error bars that reference data on the sheet, then the chart refers
to the new data, and chages when the new data changes, but the error bars
still refer to the original sheet. Is there a way that I can copy a sheet
that contains data with a chart that refers to that sheet, which includes the
error bars refering to the new sheet rather than the original?

 

[DT]

Paul.
It can be done.
You need to create a new book with the tab you want copied.
Then, in this new book, change the name of the tab. This will change the
references in the charts to this new name.
Move the newly named tab back to the original book. This will also
eliminate the new book.
Results are inconsistent unless you rename a new tab; changing the name of
the tab in the temporary new book seems to be the only way to make the chart
references consistently refer to the new tab.

Create the new book.
Right click on the tab to be copied. Select "Move or Copy".
Select the check box that says "Create a copy".
In the "To book" pulldown select "New Book". Select OK.

Move it back.
Rename the new tab in the new book.
In the new book right click on the new tab with the new name. Select "Move
or Copy".
In the "To book" pulldown select the name of your original file. Select OK.