'External links' in formulas when the user paste cells to other workbook?

[tskogstrom]

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]

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]

Hi,
sorry but it doesn't solve my problem. I reduce the users possibilities
to change into the workbook by workbook protection and I also use
formulas in 'named cells' formula field. These formulas could turn into
external links if the user copy paste certain cells.

The users workbook is protected and therefore the user can't change
back the external links (and most users will not be skilled enough to
find the links).


Regards
tskogstrom


Dave Peterson skrev:

 

[Dave Peterson]

I think the only two options are for your code to do it (edit|links|change
source) or to have the user do it manually.

I can't think of any other ways to do it.