I have been having the same problem. When you make a copy of a worksheet
within a single workbook the custom error bars do not update and still point
to the original worksheet. I figured out a work around. It's rather tedious,
but if you have a lot of charts in the worksheet with custom error bars, it
is faster than individually selecting the error bar values for each chart.
Here's what to do:
1. Make a copy of the entire workbook.
2. Delete the worksheet that has the custom error bars from the copied
3. From the original workbook, copy the worksheet with the custom error bars
to the new workbook (right click on the worksheet tab, click "Move or copy
...", choose the new workbook, check "Make a copy"). All of the cells in the
copied worksheet should reference the original workbook, but the error bars
only specify the worksheet, and not the workbook.
4. Rename the worksheet in the new work book. The error bars will update
with the new name (they only fail to update when you copy the worksheet).
5. Move (or copy) the renamed worksheet from the new workbook to the old
workbook. The cells in the worksheet will still reference the original
worksheet. Again, since the error bars did not update when the worksheet was
moved or copied, they will retain the name of the renamed worksheet without
You should now have a second worksheet in the original workbook that is
identical to the original worksheet, except that the name is changed,
including references in all cells, charts, and error bars.
Hope this one works for you.