I do this when I do it manually:

Select all the cells
edit|Replace
what: = (equal sign)
with: $$$$$
replace all

Then copy that sheet to its new home. Since all the formulas are just plain old
text, there are no links back to the original workbook.

Then I do two more edit|Replaces to change the $$$$$ back to =. (Original and
new worksheets.)

If I need code, I'd record a macro when I did it manually.

tskogstrom wrote:

Hi,

The workbook have in formulas links between sheets and it will become a
'external link' to this workbook if the user copy&paste cells to other
workbooks. I have a huge amount of formulas, cells and sheets and feel
I can't make 'VBA coded links' to all of it (like 'Let
Sheet1.Range("A1").value = Sheet2.Range("A1").value')

I want the pasted cells have the same formulas as in the first workbook
and NOT change the formula to link to the WB I copied the cells I
copied cells from.

This is even more frustrating when I also use formulas and links as
'named ranges'.

I wonder if I could exchange the formulas with a VBA function, like
instead of writing:
=Sheet2!A1*2 , I can address a function named "SheetAdd":

=SheetAdd("sht2") & A1

and in VBA have:

Function SheetAdd (str as string)
If str = "sht2" then
SheetAdd = "Sheet2!"
End if
End function

This doesn't work of cource, since '&A1' refere to A1 in the
activesheet, but ...

Suggestions?

/Regards
Tskogstrom

--

Dave Peterson