Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas to other workbooks
I have a workbook title "Book1" which contains formulas
on Sheet1. If I highlight all these formulas and paste them into Sheet1 of "Book2", then the formulas in Book2 will contain references to "Book1". How can I paste everything into "Book2" without all those references to "Book1" in the formulas??? thank u |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas to other workbooks
Hi Robert
The way I see it you have three options. You can paste the values, though you would have already thought of that. If your two worksheets are identical you could paste it across and find the references in a block something like Find '[Book1.xls]Sheet1'! Replace with nothing in the replace box This will remove all of the references to the first workbook. Finally you could break the links though this has the same result as the first option in that it hard codes everything. Anyways just some suggestions. Take care Marcus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas to other workbooks
I have 2 EXCEL 2007 files up on the screen in front of me. 1. The first one is called Robert_Crandal - the second one is called Robert_Crandal2 2. In cell C14 of:- Robert_Crandal - I have:- =(A14+B14) 3. In the above mentioned cell C14 do a Ctrl-C. 4. Now click on the other file (already open) called:- Robert_Crandal2 Go to cell C14 (for example, this can, of course, be any cell of your choice) then:- Home / Paste / Paste 5. All that now appears in cell C14 of:- Robert_Crandal2 - is:- =(A14+B14) 6. I think that the above gives you what you want. If my comments have helped please hit Yes. Thanks. "Robert Crandal" wrote: I have a workbook title "Book1" which contains formulas on Sheet1. If I highlight all these formulas and paste them into Sheet1 of "Book2", then the formulas in Book2 will contain references to "Book1". How can I paste everything into "Book2" without all those references to "Book1" in the formulas??? thank u . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas to other workbooks
Maybe in code use the ChangeLink method
This example changes a Microsoft Excel link. ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _ "c:\excel\book2.xls", xlExcelLinksMike F"Robert Crandal" wrote in message ... I have a workbook title "Book1" which contains formulas on Sheet1. If I highlight all these formulas and paste them into Sheet1 of "Book2", then the formulas in Book2 will contain references to "Book1". How can I paste everything into "Book2" without all those references to "Book1" in the formulas??? thank u |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas to other workbooks
not tested but see if this approach does what you want:
Sub CopyPasteFormulas() Dim rng As Range Dim rng2 As Range 'worksheet & range where formulas located 'change as require Set rng = ThisWorkbook.Worksheets("Sheet1").Range("C4:E8") 'destination workbook / worksheet & range 'change as required Set rng2 = Workbooks("Book2").Worksheets("Sheet1").Range("C4: E8") rng.Copy rng2.PasteSpecial _ Paste:=xlPasteFormulas, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False Application.CutCopyMode = False End Sub -- jb "Robert Crandal" wrote: I have a workbook title "Book1" which contains formulas on Sheet1. If I highlight all these formulas and paste them into Sheet1 of "Book2", then the formulas in Book2 will contain references to "Book1". How can I paste everything into "Book2" without all those references to "Book1" in the formulas??? thank u . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formulas to other workbooks
trip
What if you have =Sheet1!A14 + B14 in Robert_Crandal C14? What happens to your copy/paste to Robert_Crandal2 Gord Dibben MS Excel MVP On Thu, 17 Dec 2009 03:29:01 -0800, trip_to_tokyo wrote: I have 2 EXCEL 2007 files up on the screen in front of me. 1. The first one is called Robert_Crandal - the second one is called Robert_Crandal2 2. In cell C14 of:- Robert_Crandal - I have:- =(A14+B14) 3. In the above mentioned cell C14 do a Ctrl-C. 4. Now click on the other file (already open) called:- Robert_Crandal2 Go to cell C14 (for example, this can, of course, be any cell of your choice) then:- Home / Paste / Paste 5. All that now appears in cell C14 of:- Robert_Crandal2 - is:- =(A14+B14) 6. I think that the above gives you what you want. If my comments have helped please hit Yes. Thanks. "Robert Crandal" wrote: I have a workbook title "Book1" which contains formulas on Sheet1. If I highlight all these formulas and paste them into Sheet1 of "Book2", then the formulas in Book2 will contain references to "Book1". How can I paste everything into "Book2" without all those references to "Book1" in the formulas??? thank u . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Workbooks and keeping the formulas only? | Excel Discussion (Misc queries) | |||
Copy formulas between workbooks without copying links | Excel Discussion (Misc queries) | |||
Copy formulas between workbooks without copying links | Excel Programming | |||
Copying formulas between workbooks | Excel Discussion (Misc queries) | |||
Copying formulas between workbooks - help needed | Excel Programming |