Absolute->relative link

[sid.varno]

Hello all,

here's my problem:
I created a vba code that copy from a template 2 sheets and paste them
into a new workbook.
But in the sheet #2, there's some formulas like this: ='Valeurs NCAP'!
F3-$C3 where "Valeurs NCAP" is sheet #1.
Once the sheet copied in the new workbook, the link became:
='[Original_workbook.xls]Valeurs NCAP'!F3-$C3

How can I break this absolute link and transform it in relative link
to only keep ='Valeurs NCAP'!F3-$C3 ?

thanks in advance,
alex

 

[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)

Hello all,

here's my problem:
I created a vba code that copy from a template 2 sheets and paste them
into a new workbook.
But in the sheet #2, there's some formulas like this: ='Valeurs NCAP'!
F3-$C3 where "Valeurs NCAP" is sheet #1.
Once the sheet copied in the new workbook, the link became:
='[Original_workbook.xls]Valeurs NCAP'!F3-$C3

How can I break this absolute link and transform it in relative link
to only keep ='Valeurs NCAP'!F3-$C3 ?

thanks in advance,
alex

 

[sid.varno]

Thanks! Once I have read your answer, I have thought that it was not
what I need.
But in fact, it's!

Now, it works fine.
Thx
alex