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

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

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
>
>
> .
>

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"
<(E-Mail Removed)> wrote in message
news:rSmWm.59704$(E-Mail Removed)...
>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
>
>

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
>
>
> .
>

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 <(E-Mail Removed)>
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
>>
>>
>> .
>>