Tricky problem with Excel formulas

[dinosaur_uk]

Hi there,

I have two WorkBooks (Book1.xls and Book2.xls)

if want to copy a formula(='Sheet2'A1) from Book1.xls to the same place in Book2.xls, it paste (='[Book1.xls]Sheet2'A1). I dont want it referencing to the Book1.xls

I have tried paste special formulas only, and it does not work.

I think the best way is just to remove the = and copy past the formulas manually...

Is there any better way?

Please help!!!

[agmorgan]

If you are using macros you could just parse the formula to remove the Book1 part
e.g.

Dim x As String
    x = ActiveCell.Formula
    x = "=" & Right(x, Len(x) - Len("=[Book1]"))
    ActiveCell.Formula = x