When pasting formulas to other WB, the references still linked to old WB ...

[tskogstrom]

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

 

[Dave Peterson]

One way is to save that receiving workbook and then do edit|links and change
source.

But I like to change all my formulas to text:
(with the recorder on???)
Select all the cells
edit|replace
what: =
with: $$$$$=
replace all

Then copy the cells and paste.

Then do the edit|replace in reverse:

Select all the cells
edit|replace
what: $$$$$=
with: =
replace all

(In both worksheets)

========
If you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp



And to look for links to see if you fixed things correctly:

Bill Manville's FindLink program can find it:
http://www.oaltd.co.uk/MVP/Default.htm

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

 

[tskogstrom]

hi,

if I understand you right, you suggest I should replace all formulas
to a syntax excel doesn't recognise as formulas, do the WB change and
pasting, and then restore the formulas. i'll think about that. That
doesn't help the other users (not VBA users ...), of cource but could
help me from time to time.

The Name Manager do I use already, but there is the same: Other users
doesn't and they arn't even aware of the problem with the external
named range links.

It seem's like no solutions, if not anybody other have any
suggestions?

Kind regards
Tskogstrom