Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Crosstabs do not copy as I would like when copying worksheets
When I make a copy of a worksheet (into a new tab in the same workbook) that
contains a crosstab, the cell references of the crosstab retain the reference to the original (old) worksheet. This is not what I want. In other words, when I copy a worksheet that contains crosstabs, I have to go into each crosstab on the new worksheet and reset all the cell references to point to the new worksheet (instead of the old/prior/original worksheet). 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 CROSTABS!! Is there any way to have the crosstab 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". Sorry for the long post - it's hard to explain. Using Excel 2003 SP2. Thanks, Peter -- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Crosstabs do not copy as I would like when copying worksheets
Update/Correction: When I say "crosstab", I maybe should have said
"PivotTable". Same thing in my brain, but maybe not for others. Sorry - Peter -- "PSchmidt" wrote: When I make a copy of a worksheet (into a new tab in the same workbook) that contains a crosstab, the cell references of the crosstab retain the reference to the original (old) worksheet. This is not what I want. In other words, when I copy a worksheet that contains crosstabs, I have to go into each crosstab on the new worksheet and reset all the cell references to point to the new worksheet (instead of the old/prior/original worksheet). 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 CROSTABS!! Is there any way to have the crosstab 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". Sorry for the long post - it's hard to explain. Using Excel 2003 SP2. Thanks, Peter -- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Crosstabs do not copy as I would like when copying worksheets
Hi
It depends upon how you are doing the copy. If you have Sheet1 with a formula in A1 which say =Sheet1!B1, then if you mark all the cells of the sheet (Ctrl+A)Copymove to sheet2Paste the Sheet2 cell A1 will say =Sheet1!A1. However, if you hold down the Control button, click on the sheet tab and drag to the right, this will create a copy of Sheet1 and it will be titled by default as Sheet1 (2) Cell A will have the formula ='Sheet1 (2)!'A1 which when you rename the sheet to say "new" will become =new!A1. If you are wanting to make the copy in another workbook, then right click on the sheet tabMove or Copyclick make copySelect Destination workbook and the behaviour will also be as described above. -- Regards Roger Govier "PSchmidt" wrote in message ... When I make a copy of a worksheet (into a new tab in the same workbook) that contains a crosstab, the cell references of the crosstab retain the reference to the original (old) worksheet. This is not what I want. In other words, when I copy a worksheet that contains crosstabs, I have to go into each crosstab on the new worksheet and reset all the cell references to point to the new worksheet (instead of the old/prior/original worksheet). 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 CROSTABS!! Is there any way to have the crosstab 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". Sorry for the long post - it's hard to explain. Using Excel 2003 SP2. Thanks, Peter -- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Crosstabs do not copy as I would like when copying worksheets
Roger, thanks for your response. I agree with your thinking for "normal"
cells, but NOT FOR PIVITTABLES!! (I believe). For example, if I make a copy of sheet1 (which I have named "01-07"), the Pivot table references cell range "'01-07'!$A$1:$C$6". And when I make a copy of the sheet, even using the Ctrl-Key you suggested, the name of the new worksheet is "01-07 (2)" as you would expect, but the pivot table STILL REFERENCES the old range of "'01-07'!$A$1:$C$6" and NOT "'01-07 (2)'!$A$1:$C$6" as I would like. Doesn't it work this way for you ?? Note: This has been driving me crazy for years. Thanks, Peter __________________________________________________ ___ "Roger Govier" wrote: Hi It depends upon how you are doing the copy. If you have Sheet1 with a formula in A1 which say =Sheet1!B1, then if you mark all the cells of the sheet (Ctrl+A)Copymove to sheet2Paste the Sheet2 cell A1 will say =Sheet1!A1. However, if you hold down the Control button, click on the sheet tab and drag to the right, this will create a copy of Sheet1 and it will be titled by default as Sheet1 (2) Cell A will have the formula ='Sheet1 (2)!'A1 which when you rename the sheet to say "new" will become =new!A1. If you are wanting to make the copy in another workbook, then right click on the sheet tabMove or Copyclick make copySelect Destination workbook and the behaviour will also be as described above. -- Regards Roger Govier "PSchmidt" wrote in message ... When I make a copy of a worksheet (into a new tab in the same workbook) that contains a crosstab, the cell references of the crosstab retain the reference to the original (old) worksheet. This is not what I want. In other words, when I copy a worksheet that contains crosstabs, I have to go into each crosstab on the new worksheet and reset all the cell references to point to the new worksheet (instead of the old/prior/original worksheet). 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 CROSTABS!! Is there any way to have the crosstab 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". Sorry for the long post - it's hard to explain. Using Excel 2003 SP2. Thanks, Peter -- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Crosstabs do not copy as I would like when copying worksheets
Hi
I had not realised you were talking about Pivot Tables till I saw your later posting. Yes, it does do the same for me if I copy a PT by either method, but it is a simple task to change On the new PT, right click on the PTWizardBackData amend "'01-07 '!$A$1:$C$6" to "'01-07 (2)'!$A$1:$C$6" Incidentally, are you the P Schmidt I know from Gloucestershire? -- Regards Roger Govier "PSchmidt" wrote in message ... Roger, thanks for your response. I agree with your thinking for "normal" cells, but NOT FOR PIVITTABLES!! (I believe). For example, if I make a copy of sheet1 (which I have named "01-07"), the Pivot table references cell range "'01-07'!$A$1:$C$6". And when I make a copy of the sheet, even using the Ctrl-Key you suggested, the name of the new worksheet is "01-07 (2)" as you would expect, but the pivot table STILL REFERENCES the old range of "'01-07'!$A$1:$C$6" and NOT "'01-07 (2)'!$A$1:$C$6" as I would like. Doesn't it work this way for you ?? Note: This has been driving me crazy for years. Thanks, Peter __________________________________________________ ___ "Roger Govier" wrote: Hi It depends upon how you are doing the copy. If you have Sheet1 with a formula in A1 which say =Sheet1!B1, then if you mark all the cells of the sheet (Ctrl+A)Copymove to sheet2Paste the Sheet2 cell A1 will say =Sheet1!A1. However, if you hold down the Control button, click on the sheet tab and drag to the right, this will create a copy of Sheet1 and it will be titled by default as Sheet1 (2) Cell A will have the formula ='Sheet1 (2)!'A1 which when you rename the sheet to say "new" will become =new!A1. If you are wanting to make the copy in another workbook, then right click on the sheet tabMove or Copyclick make copySelect Destination workbook and the behaviour will also be as described above. -- Regards Roger Govier "PSchmidt" wrote in message ... When I make a copy of a worksheet (into a new tab in the same workbook) that contains a crosstab, the cell references of the crosstab retain the reference to the original (old) worksheet. This is not what I want. In other words, when I copy a worksheet that contains crosstabs, I have to go into each crosstab on the new worksheet and reset all the cell references to point to the new worksheet (instead of the old/prior/original worksheet). 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 CROSTABS!! Is there any way to have the crosstab 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". Sorry for the long post - it's hard to explain. Using Excel 2003 SP2. Thanks, Peter -- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Crosstabs do not copy as I would like when copying worksheets
Right - it's simple to change, except that after doing it about 2,000,000
times (ok - slight exaggeration) it get's tiring and time consuming. I'm happy to hear that I don't appear to be missing something obvious. It just seems to me that the way it works is counter intuitive. I am NOT the P Schmidt you know from Gloucestershire, but I still really appreciate your feedback. Thanks, Peter __________________________________________________ ________________ "Roger Govier" wrote: Hi I had not realised you were talking about Pivot Tables till I saw your later posting. Yes, it does do the same for me if I copy a PT by either method, but it is a simple task to change On the new PT, right click on the PTWizardBackData amend "'01-07 '!$A$1:$C$6" to "'01-07 (2)'!$A$1:$C$6" Incidentally, are you the P Schmidt I know from Gloucestershire? -- Regards Roger Govier "PSchmidt" wrote in message ... Roger, thanks for your response. I agree with your thinking for "normal" cells, but NOT FOR PIVITTABLES!! (I believe). For example, if I make a copy of sheet1 (which I have named "01-07"), the Pivot table references cell range "'01-07'!$A$1:$C$6". And when I make a copy of the sheet, even using the Ctrl-Key you suggested, the name of the new worksheet is "01-07 (2)" as you would expect, but the pivot table STILL REFERENCES the old range of "'01-07'!$A$1:$C$6" and NOT "'01-07 (2)'!$A$1:$C$6" as I would like. Doesn't it work this way for you ?? Note: This has been driving me crazy for years. Thanks, Peter __________________________________________________ ___ "Roger Govier" wrote: Hi It depends upon how you are doing the copy. If you have Sheet1 with a formula in A1 which say =Sheet1!B1, then if you mark all the cells of the sheet (Ctrl+A)Copymove to sheet2Paste the Sheet2 cell A1 will say =Sheet1!A1. However, if you hold down the Control button, click on the sheet tab and drag to the right, this will create a copy of Sheet1 and it will be titled by default as Sheet1 (2) Cell A will have the formula ='Sheet1 (2)!'A1 which when you rename the sheet to say "new" will become =new!A1. If you are wanting to make the copy in another workbook, then right click on the sheet tabMove or Copyclick make copySelect Destination workbook and the behaviour will also be as described above. -- Regards Roger Govier "PSchmidt" wrote in message ... When I make a copy of a worksheet (into a new tab in the same workbook) that contains a crosstab, the cell references of the crosstab retain the reference to the original (old) worksheet. This is not what I want. In other words, when I copy a worksheet that contains crosstabs, I have to go into each crosstab on the new worksheet and reset all the cell references to point to the new worksheet (instead of the old/prior/original worksheet). 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 CROSTABS!! Is there any way to have the crosstab 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". Sorry for the long post - it's hard to explain. Using Excel 2003 SP2. Thanks, Peter -- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Crosstabs do not copy as I would like when copying worksheets
Hi
Maybe counter-intuitive, but for a reason. When you create multiple Pivot Tables, XL asks if you want to base the second, 3rd tables etc. upon the data range used for the first. This reduces file size by having a smaller Pivot cache, and gives a single cache to refresh. I guess that hewn you copy a PT, XL thinks you may be wanting to just "pivot" the same data another way - after all, that's what it's all about - and tries to conserve resources. On the other hand, I may be crediting the MS designers with too much thought<vbg So, annoying at times, but understandable. -- Regards Roger Govier "PSchmidt" wrote in message ... Right - it's simple to change, except that after doing it about 2,000,000 times (ok - slight exaggeration) it get's tiring and time consuming. I'm happy to hear that I don't appear to be missing something obvious. It just seems to me that the way it works is counter intuitive. I am NOT the P Schmidt you know from Gloucestershire, but I still really appreciate your feedback. Thanks, Peter __________________________________________________ ________________ "Roger Govier" wrote: Hi I had not realised you were talking about Pivot Tables till I saw your later posting. Yes, it does do the same for me if I copy a PT by either method, but it is a simple task to change On the new PT, right click on the PTWizardBackData amend "'01-07 '!$A$1:$C$6" to "'01-07 (2)'!$A$1:$C$6" Incidentally, are you the P Schmidt I know from Gloucestershire? -- Regards Roger Govier "PSchmidt" wrote in message ... Roger, thanks for your response. I agree with your thinking for "normal" cells, but NOT FOR PIVITTABLES!! (I believe). For example, if I make a copy of sheet1 (which I have named "01-07"), the Pivot table references cell range "'01-07'!$A$1:$C$6". And when I make a copy of the sheet, even using the Ctrl-Key you suggested, the name of the new worksheet is "01-07 (2)" as you would expect, but the pivot table STILL REFERENCES the old range of "'01-07'!$A$1:$C$6" and NOT "'01-07 (2)'!$A$1:$C$6" as I would like. Doesn't it work this way for you ?? Note: This has been driving me crazy for years. Thanks, Peter __________________________________________________ ___ "Roger Govier" wrote: Hi It depends upon how you are doing the copy. If you have Sheet1 with a formula in A1 which say =Sheet1!B1, then if you mark all the cells of the sheet (Ctrl+A)Copymove to sheet2Paste the Sheet2 cell A1 will say =Sheet1!A1. However, if you hold down the Control button, click on the sheet tab and drag to the right, this will create a copy of Sheet1 and it will be titled by default as Sheet1 (2) Cell A will have the formula ='Sheet1 (2)!'A1 which when you rename the sheet to say "new" will become =new!A1. If you are wanting to make the copy in another workbook, then right click on the sheet tabMove or Copyclick make copySelect Destination workbook and the behaviour will also be as described above. -- Regards Roger Govier "PSchmidt" wrote in message ... When I make a copy of a worksheet (into a new tab in the same workbook) that contains a crosstab, the cell references of the crosstab retain the reference to the original (old) worksheet. This is not what I want. In other words, when I copy a worksheet that contains crosstabs, I have to go into each crosstab on the new worksheet and reset all the cell references to point to the new worksheet (instead of the old/prior/original worksheet). 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 CROSTABS!! Is there any way to have the crosstab 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". Sorry for the long post - it's hard to explain. Using Excel 2003 SP2. Thanks, Peter -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
crosstabs | Excel Discussion (Misc queries) | |||
copy between worksheets does not copy formulae just values | Excel Discussion (Misc queries) | |||
Copying to other worksheets | Excel Discussion (Misc queries) | |||
need pivot tables to look more like crosstabs with repeating data. | Excel Discussion (Misc queries) | |||
copying worksheets | Excel Discussion (Misc queries) |