When you make cell references to other worksheets, you will see the
sheetname in the formula.

I often copy and paste the formulas to another workbook and these
formulas now also show the reference to the old workbook. =Sheet2!D10
-- =[test.xls]Sheet2!D10

I want it to point to the reference cells in the new worksheet I
pasted to:
=Sheet2!D10 -- =Sheet2!D10

I find this to be a big problem, because when I develop new versions
of VBA code the workbook and design changes, I what to update it with
all old valuses and formulas in the earlier version. Sometime also I
have a empty template workbook and what to add all data from another
calculation workbook - the users not knowing VBA, Excel find and
replace functions etc also experience this problem.

Even more troublesome will it be - I have some named ranges I have
formulas into, that include references to sheets. When pasting these
referenced cells, the named range is pasted as well - and it should!
But not with a reference to the old workbook. It is almost impossible
to find these external references for an ordinary user and completly
impossible considering they are hidden and the workbook is
protected...

Is there a way with VBA code procedured to cover this events? Or is
there some kind of different modes of named ranges, like global or
sheets that make a difference in this matter?

Anybody having a solution? Today, I myself had to run find and replace
each WS to get rid of the workbook reference and search my had through
the named ranges to find the external links. Tools like Navigator
utilities doesn't help here either...

Kind regards
Tskogstrom