Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Copy formulas between workbooks without copying links
I want to copy formulas from a range in worksheet A in workbook A to a
range in worksheet B in workbook B. When i use the clipboard to copy and paste special by formulas, I often create unwanted external links in workbook B (eg. some names in workbook B could refer to the ranges in workbook A now). While i could possible fix this later by looking for the external links and delete them, I'm looking for a simple way to avoid it. One possible way is do it in VBA: workbooks(2).sheets(1).range("A1:A10").formula = workbooks(1).sheets(1).range("A1:A10").formula but looks like this only works for non-array formulas thanks a lot, any help is much appreciated. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Copy formulas between workbooks without copying links
It sometimes help to do the copy using the fx box at the top of the
worksheet. click on the cell you want to copy. then highlight the formula in the Fx box and do a copy. then paste the formula in the Fx box in the 2nd worksheet. "MCI" wrote: I want to copy formulas from a range in worksheet A in workbook A to a range in worksheet B in workbook B. When i use the clipboard to copy and paste special by formulas, I often create unwanted external links in workbook B (eg. some names in workbook B could refer to the ranges in workbook A now). While i could possible fix this later by looking for the external links and delete them, I'm looking for a simple way to avoid it. One possible way is do it in VBA: workbooks(2).sheets(1).range("A1:A10").formula = workbooks(1).sheets(1).range("A1:A10").formula but looks like this only works for non-array formulas thanks a lot, any help is much appreciated. |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Copy formulas between workbooks without copying links
One simple method is to use EditReplace a couple times.
EditReplace What: = With: ^^^ Replace all. Copy then reverse the process on the target book. Close the source book without saving or reverse there also. Gord Dibben MS Excel MVP On Sat, 3 May 2008 19:53:29 -0700 (PDT), MCI wrote: I want to copy formulas from a range in worksheet A in workbook A to a range in worksheet B in workbook B. When i use the clipboard to copy and paste special by formulas, I often create unwanted external links in workbook B (eg. some names in workbook B could refer to the ranges in workbook A now). While i could possible fix this later by looking for the external links and delete them, I'm looking for a simple way to avoid it. One possible way is do it in VBA: workbooks(2).sheets(1).range("A1:A10").formula = workbooks(1).sheets(1).range("A1:A10").formula but looks like this only works for non-array formulas thanks a lot, any help is much appreciated. |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Copy formulas between workbooks without copying links
thanks a lot, but this doesn't seem to work for array formulas.
for example, in source workbook, range("D9:D10") has an array formula of "=A1:A2", using your way described below, in destination workbook, we would have: cell D9 = "=A1:A2" cell D10 = "=A1:A2" which gives "#VALUE!" errors and is different from the source workbook. On May 4, 1:39 pm, Gord Dibben <gorddibbATshawDOTca wrote: One simple method is to use EditReplace a couple times. EditReplace What: = With: ^^^ Replace all. Copythen reverse the process on the target book. Close the source book without saving or reverse there also. Gord Dibben MS Excel MVP On Sat, 3 May 2008 19:53:29 -0700 (PDT), MCI wrote: I want tocopyformulasfrom a range in worksheet A in workbook A to a range in worksheet B in workbook B. When i use the clipboard tocopyand paste special byformulas, I often create unwanted external links in workbook B (eg. some names in workbook B could refer to the ranges in workbook A now). While i could possible fix this later by looking for the external links and delete them, I'm looking for a simple way to avoid it. One possible way is do it in VBA: workbooks(2).sheets(1).range("A1:A10").formula = workbooks(1).sheets(1).range("A1:A10").formula but looks like this only works for non-arrayformulas thanks a lot, any help is much appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formulas between workbooks without copying links
Too late at night for me to understand your version of an array formula
{=A1:A2} entered in D9:D10 Gord On Sun, 4 May 2008 20:52:46 -0700 (PDT), MCI wrote: thanks a lot, but this doesn't seem to work for array formulas. for example, in source workbook, range("D9:D10") has an array formula of "=A1:A2", using your way described below, in destination workbook, we would have: cell D9 = "=A1:A2" cell D10 = "=A1:A2" which gives "#VALUE!" errors and is different from the source workbook. On May 4, 1:39 pm, Gord Dibben <gorddibbATshawDOTca wrote: One simple method is to use EditReplace a couple times. EditReplace What: = With: ^^^ Replace all. Copythen reverse the process on the target book. Close the source book without saving or reverse there also. Gord Dibben MS Excel MVP On Sat, 3 May 2008 19:53:29 -0700 (PDT), MCI wrote: I want tocopyformulasfrom a range in worksheet A in workbook A to a range in worksheet B in workbook B. When i use the clipboard tocopyand paste special byformulas, I often create unwanted external links in workbook B (eg. some names in workbook B could refer to the ranges in workbook A now). While i could possible fix this later by looking for the external links and delete them, I'm looking for a simple way to avoid it. One possible way is do it in VBA: workbooks(2).sheets(1).range("A1:A10").formula = workbooks(1).sheets(1).range("A1:A10").formula but looks like this only works for non-arrayformulas thanks a lot, any help is much appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formulas between workbooks without copying links
sorry for any confusion.
the array formula in my post is entered as this: 1. select cells D9 through D10 2. enter the following formula in the formula bar: =A1:A2 3. Press CTRL+SHIFT+ENTER. Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formulas between workbooks without copying links
When I copy D9:D10 from source book to D9:D10 in a new workbook, I do not get
the link to the source book. What copies is {=A1:A2} in D9 and {=A1:A2} in D10 Gord On Mon, 5 May 2008 18:08:46 -0700 (PDT), MCI wrote: sorry for any confusion. the array formula in my post is entered as this: 1. select cells D9 through D10 2. enter the following formula in the formula bar: =A1:A2 3. Press CTRL+SHIFT+ENTER. Thanks. |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Copy formulas between workbooks without copying links
Great tip, works a treat and saved me a pile of work when I'd mucked up the
formulas on a workbook that I'd used as the template for 15 others. "Gord Dibben" wrote: One simple method is to use EditReplace a couple times. EditReplace What: = With: ^^^ Replace all. Copy then reverse the process on the target book. Close the source book without saving or reverse there also. Gord Dibben MS Excel MVP On Sat, 3 May 2008 19:53:29 -0700 (PDT), MCI wrote: I want to copy formulas from a range in worksheet A in workbook A to a range in worksheet B in workbook B. When i use the clipboard to copy and paste special by formulas, I often create unwanted external links in workbook B (eg. some names in workbook B could refer to the ranges in workbook A now). While i could possible fix this later by looking for the external links and delete them, I'm looking for a simple way to avoid it. One possible way is do it in VBA: workbooks(2).sheets(1).range("A1:A10").formula = workbooks(1).sheets(1).range("A1:A10").formula but looks like this only works for non-array formulas thanks a lot, any help is much appreciated. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy formulas between workbooks without copying links
i was not saying copying the formulas creates a link to the source
workbook. What i meant is using the method you described can't replicate what the source workbook has in the destination workbook. (meaning: cell D9 = "=A1:A2", cell D10 = "=A1:A2" in destination workbook, and these formulas are not array formulas. but cell D9 and D10 in source workbook have an array formula and you can't change D9 or D10 individually) By "copyD9:D10 from source book to D9:D10 in a new workbook", you mean using select - copy - paste by formula or your method (EditReplace / What: = / With: ^^^)? If it's your method, source workbook and destination workbook are not the same. thanks. Replace all. On May 6, 12:11 pm, Gord Dibben <gorddibbATshawDOTca wrote: When IcopyD9:D10 from source book to D9:D10 in a new workbook, I do not get the link to the source book. What copies is {=A1:A2} in D9 and {=A1:A2} in D10 Gord On Mon, 5 May 2008 18:08:46 -0700 (PDT), MCI wrote: sorry for any confusion. the array formula in my post is entered as this: 1. select cells D9 through D10 2. enter the following formula in the formula bar: =A1:A2 3. Press CTRL+SHIFT+ENTER. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formulas between workbooks | Excel Discussion (Misc queries) | |||
Copy workbooks with links | Excel Discussion (Misc queries) | |||
Copy links between excel workbooks using citrix? | Excel Worksheet Functions | |||
Shortcut in copying a formula that links 2 workbooks | Excel Discussion (Misc queries) | |||
My sumif formulas containin links to other workbooks do not calcul | Excel Worksheet Functions |