Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a worksheet to another workbook
I am trying to copy a worksheet A to another workbook. The original worksheet
A has formulas such as ='Store P&LE11, where Store P&L is another worksheet in the same workbook. The new workbook to which I plan to copy worksheet A also has a worksheet named Store P&L. I want the copied worksheet to retain the original formula reference so that it will refer to Store P&L in the destination worksheet. Instead, I am getting a link reference back to worksheet A's workbook when I copy the worksheet to its destination. How can I fix this? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a worksheet to another workbook
That formula is not legal so I guess there must be a typo or two but aside from
that, one way to copy to another workbook is to copy the formulas as text then change back to formulas. Select formulas in source sheet and EditReplace What: = With: ^^^ Replace all. Copy the altered results to the target workbook then reverse the EditReplace in that workbook. Close source workbook without saving or reverse the editrplace there also. Gord Dibben MS Excel MVP On Thu, 9 Aug 2007 10:42:05 -0700, Dutch wrote: I am trying to copy a worksheet A to another workbook. The original worksheet A has formulas such as ='Store P&LE11, where Store P&L is another worksheet in the same workbook. The new workbook to which I plan to copy worksheet A also has a worksheet named Store P&L. I want the copied worksheet to retain the original formula reference so that it will refer to Store P&L in the destination worksheet. Instead, I am getting a link reference back to worksheet A's workbook when I copy the worksheet to its destination. How can I fix this? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a worksheet to another workbook
I have done as you said for the entire worksheet. I have then used the
command to copy the sheet to a destination workbook. The error message I get is that I have cells with more than 255 characters, which it states will be lost. Solution? Another solution suggested was to simply copy the worksheet as above, then copy the descriptive reference to the old workbook from one of the formulas into Find, Replace with blank, then Replace All. The problem I have with this is I can't seem to copy that segment of the formula into the Find box. What am I doing wrong? "Gord Dibben" wrote: That formula is not legal so I guess there must be a typo or two but aside from that, one way to copy to another workbook is to copy the formulas as text then change back to formulas. Select formulas in source sheet and EditReplace What: = With: ^^^ Replace all. Copy the altered results to the target workbook then reverse the EditReplace in that workbook. Close source workbook without saving or reverse the editrplace there also. Gord Dibben MS Excel MVP On Thu, 9 Aug 2007 10:42:05 -0700, Dutch wrote: I am trying to copy a worksheet A to another workbook. The original worksheet A has formulas such as ='Store P&LE11, where Store P&L is another worksheet in the same workbook. The new workbook to which I plan to copy worksheet A also has a worksheet named Store P&L. I want the copied worksheet to retain the original formula reference so that it will refer to Store P&L in the destination worksheet. Instead, I am getting a link reference back to worksheet A's workbook when I copy the worksheet to its destination. How can I fix this? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a worksheet to another workbook
On the second solution, I managed to type the old workbook reference into
Replace What, left With blank, then Replace All. It seemed to work until it hit a long formula with a lot of those references and the stopped with a message "Formula too long." What can I do? "Dutch" wrote: I have done as you said for the entire worksheet. I have then used the command to copy the sheet to a destination workbook. The error message I get is that I have cells with more than 255 characters, which it states will be lost. Solution? Another solution suggested was to simply copy the worksheet as above, then copy the descriptive reference to the old workbook from one of the formulas into Find, Replace with blank, then Replace All. The problem I have with this is I can't seem to copy that segment of the formula into the Find box. What am I doing wrong? "Gord Dibben" wrote: That formula is not legal so I guess there must be a typo or two but aside from that, one way to copy to another workbook is to copy the formulas as text then change back to formulas. Select formulas in source sheet and EditReplace What: = With: ^^^ Replace all. Copy the altered results to the target workbook then reverse the EditReplace in that workbook. Close source workbook without saving or reverse the editrplace there also. Gord Dibben MS Excel MVP On Thu, 9 Aug 2007 10:42:05 -0700, Dutch wrote: I am trying to copy a worksheet A to another workbook. The original worksheet A has formulas such as ='Store P&LE11, where Store P&L is another worksheet in the same workbook. The new workbook to which I plan to copy worksheet A also has a worksheet named Store P&L. I want the copied worksheet to retain the original formula reference so that it will refer to Store P&L in the destination worksheet. Instead, I am getting a link reference back to worksheet A's workbook when I copy the worksheet to its destination. How can I fix this? Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a worksheet to another workbook
Do you have more than one instance of Excel running with a workbook open in
each? Close one instance and open both workbooks in the same instance and the copy should work without the truncation message. Gord On Thu, 9 Aug 2007 12:36:02 -0700, Dutch wrote: I have done as you said for the entire worksheet. I have then used the command to copy the sheet to a destination workbook. The error message I get is that I have cells with more than 255 characters, which it states will be lost. Solution? Another solution suggested was to simply copy the worksheet as above, then copy the descriptive reference to the old workbook from one of the formulas into Find, Replace with blank, then Replace All. The problem I have with this is I can't seem to copy that segment of the formula into the Find box. What am I doing wrong? "Gord Dibben" wrote: That formula is not legal so I guess there must be a typo or two but aside from that, one way to copy to another workbook is to copy the formulas as text then change back to formulas. Select formulas in source sheet and EditReplace What: = With: ^^^ Replace all. Copy the altered results to the target workbook then reverse the EditReplace in that workbook. Close source workbook without saving or reverse the editrplace there also. Gord Dibben MS Excel MVP On Thu, 9 Aug 2007 10:42:05 -0700, Dutch wrote: I am trying to copy a worksheet A to another workbook. The original worksheet A has formulas such as ='Store P&LE11, where Store P&L is another worksheet in the same workbook. The new workbook to which I plan to copy worksheet A also has a worksheet named Store P&L. I want the copied worksheet to retain the original formula reference so that it will refer to Store P&L in the destination worksheet. Instead, I am getting a link reference back to worksheet A's workbook when I copy the worksheet to its destination. How can I fix this? Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a worksheet to another workbook
Gord,
Did as you said within one instance. Copy Sheet produced the truncation message and did in fact truncate. Solution? "Gord Dibben" wrote: Do you have more than one instance of Excel running with a workbook open in each? Close one instance and open both workbooks in the same instance and the copy should work without the truncation message. Gord On Thu, 9 Aug 2007 12:36:02 -0700, Dutch wrote: I have done as you said for the entire worksheet. I have then used the command to copy the sheet to a destination workbook. The error message I get is that I have cells with more than 255 characters, which it states will be lost. Solution? Another solution suggested was to simply copy the worksheet as above, then copy the descriptive reference to the old workbook from one of the formulas into Find, Replace with blank, then Replace All. The problem I have with this is I can't seem to copy that segment of the formula into the Find box. What am I doing wrong? "Gord Dibben" wrote: That formula is not legal so I guess there must be a typo or two but aside from that, one way to copy to another workbook is to copy the formulas as text then change back to formulas. Select formulas in source sheet and EditReplace What: = With: ^^^ Replace all. Copy the altered results to the target workbook then reverse the EditReplace in that workbook. Close source workbook without saving or reverse the editrplace there also. Gord Dibben MS Excel MVP On Thu, 9 Aug 2007 10:42:05 -0700, Dutch wrote: I am trying to copy a worksheet A to another workbook. The original worksheet A has formulas such as ='Store P&LE11, where Store P&L is another worksheet in the same workbook. The new workbook to which I plan to copy worksheet A also has a worksheet named Store P&L. I want the copied worksheet to retain the original formula reference so that it will refer to Store P&L in the destination worksheet. Instead, I am getting a link reference back to worksheet A's workbook when I copy the worksheet to its destination. How can I fix this? Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying a worksheet to another workbook
Open both workbooks in the same instance of excel.
Select the worksheet to copy in the "sending" workbook. Change all the formulas to text using that technique that Gord suggested. Select all the cells edit|Replace What: = With: ^^^ Replace all Copy that sheet (edit|Move or copy sheet) to the other workbook. Ignore the error about the truncation. Back to the original workbook/worksheet. Select all the cells edit|copy To the new workbook/worksheet Select A1 Edit|Paste With all the cells selected in that new worksheet, it's time to change the strings back to formulas: Select all the cells edit|Replace What: ^^^ With: = Replace all And do the same in the original worksheet, too. (Or close it without saving.) Dutch wrote: I am trying to copy a worksheet A to another workbook. The original worksheet A has formulas such as ='Store P&LE11, where Store P&L is another worksheet in the same workbook. The new workbook to which I plan to copy worksheet A also has a worksheet named Store P&L. I want the copied worksheet to retain the original formula reference so that it will refer to Store P&L in the destination worksheet. Instead, I am getting a link reference back to worksheet A's workbook when I copy the worksheet to its destination. How can I fix this? Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting and copying data to another worksheet in a workbook. | Excel Discussion (Misc queries) | |||
Copying a worksheet into the same workbook | New Users to Excel | |||
Copying A Worksheet From Each Open Workbook to an new Workbook | Excel Worksheet Functions | |||
Copying the format within a worksheet to all in a workbook | Excel Discussion (Misc queries) | |||
Copying worksheet from workbook to another | Excel Worksheet Functions |