On Oct 26, 6:32 am, "André" wrote:
Hi,

I have a macro that updates values and formulas from another workbook. When
copy a range from Book1 to Book2 I get the file-reference in Book2, which I
want to remove. The formula I copy from Book1 refers to a cell on another
sheet in the same workbook. When I copy this formula to Book2, the formula
refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and also
tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or copy
(with vba code) the formula in a way that not includes the path+filename
from Book1.

André

Try this:
Dim sFormula As String
Dim rCell As Excel.Range

For Each rCell In ActiveCell.CurrentRegion
sFormula = rCell.Formula
sFormula = Replace(sFormula, "[Book1.xls]", "")
rCell.Formula = sFormula
Next