P
PSchmidt
When I make a copy of a worksheet (into a new tab in the same workbook) that
contains a Pivottable, the cell references of the Pivottable retain the
reference to the original (old) worksheet. This is not what I want.
In other words, when I copy a worksheet that contains Pivottable, I have to
go into each Pivottable on the new worksheet and reset all the cell
references to point to the new worksheet (instead of the old/prior/original
worksheet).
For example, if I create a Pivottable in sheet1, the Pivottable references
cell range "'sheet1'!$A$1:$C$6". And when I make a copy of the sheet, the
name of the new worksheet is "sheet1 (2)" as you would expect, but the pivot
table STILL REFERENCES the old range of "'sheet1'!$A$1:$C$6" and NOT "'sheet1
(2)'!$A$1:$C$6" as I would like.
This appears to me to be a different behavior then a regular "cell". If the
original cell references "A1" on the old worksheet, after the copy, the same
cell references "A1" on the NEW worksheet. But NOT FOR PIVOTTABLES!!
Is there any way to have the Pivottable reference the NEW worksheet instead
of the original worksheet ?
FYI, The technique I use to copy a worksheet is to right-click the tab at
the bottom - "move or copy" - "Move to end" - check "Create a copy". I can
also copy the sheet by holding down the Control button, clicking on the sheet
tab and draging to the right. This creates a copy of Sheet1 and it will be
titled by default as Sheet1 (2) but the PIVOTTABLE is still a problem.
Sorry for the long post - it's hard to explain. I'm using Excel 2003 SP2.
This problem has been driving me crazy for years.
Note: I posted this question earlier, but I used the term "crosstab" instead
of Pivittable (shows my age), so I though I would try and repost with the
correct term.
Thanks,
Peter
_________________________________________________________________
contains a Pivottable, the cell references of the Pivottable retain the
reference to the original (old) worksheet. This is not what I want.
In other words, when I copy a worksheet that contains Pivottable, I have to
go into each Pivottable on the new worksheet and reset all the cell
references to point to the new worksheet (instead of the old/prior/original
worksheet).
For example, if I create a Pivottable in sheet1, the Pivottable references
cell range "'sheet1'!$A$1:$C$6". And when I make a copy of the sheet, the
name of the new worksheet is "sheet1 (2)" as you would expect, but the pivot
table STILL REFERENCES the old range of "'sheet1'!$A$1:$C$6" and NOT "'sheet1
(2)'!$A$1:$C$6" as I would like.
This appears to me to be a different behavior then a regular "cell". If the
original cell references "A1" on the old worksheet, after the copy, the same
cell references "A1" on the NEW worksheet. But NOT FOR PIVOTTABLES!!
Is there any way to have the Pivottable reference the NEW worksheet instead
of the original worksheet ?
FYI, The technique I use to copy a worksheet is to right-click the tab at
the bottom - "move or copy" - "Move to end" - check "Create a copy". I can
also copy the sheet by holding down the Control button, clicking on the sheet
tab and draging to the right. This creates a copy of Sheet1 and it will be
titled by default as Sheet1 (2) but the PIVOTTABLE is still a problem.
Sorry for the long post - it's hard to explain. I'm using Excel 2003 SP2.
This problem has been driving me crazy for years.
Note: I posted this question earlier, but I used the term "crosstab" instead
of Pivittable (shows my age), so I though I would try and repost with the
correct term.
Thanks,
Peter
_________________________________________________________________