Tricky problem with Excel formulas

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!!!

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

VB Code:

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