Correction

[Eric]

Pardon me, there was a typo in the way I phrased the original question. Here
is the update:

I have a file called Book1. In cell C1 on Sheet2 there is an equation that
reads:

=Sheet1!A1+10

I have another file called Book2. When I copy the above equation into cell
C1 on Sheet2 of Book2, it pastes in the following:

=[Book1]Sheet1!A1+10

So Excel wants to make a reference back to Sheet1!A1 in the original file.

I want to copy the equation over to the new book, but just have it make
reference to Sheet1!A1 in the new book, not back to the original file. It
seems like the reference to the sheet is absolute by default, but I want it
to be relative.

Is there a way to get Excel to copy a sheet reference from one file to
another without making reference back to the original file?

Thanks for any help.

 

[Gary''s Student]

Try this macro:

Sub formulaize()
Dim r As Range
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Workbooks("Book1.xls")
Set wb2 = Workbooks("Book2.xls")

wb1.Activate
Sheets("Sheet2").Activate
MsgBox (" ")
Dim s As String
For Each r In ActiveSheet.UsedRange
If r.HasFormula Then
addy = r.Address
s = r.Formula
wb2.Sheets("Sheet2").Range(addy).Formula = s
End If
Next
End Sub

 

[Eric]

Thanks for the help. I was hoping there was an Options setting change I could
use to change how Excel copies sheet references from one file to another. Is
a macro the only way to handle this?

Thanks - Eric

Try this macro:

Sub formulaize()
Dim r As Range
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Workbooks("Book1.xls")
Set wb2 = Workbooks("Book2.xls")

wb1.Activate
Sheets("Sheet2").Activate
MsgBox (" ")
Dim s As String
For Each r In ActiveSheet.UsedRange
If r.HasFormula Then
addy = r.Address
s = r.Formula
wb2.Sheets("Sheet2").Range(addy).Formula = s
End If
Next
End Sub

--
Gary''s Student - gsnu200906

Pardon me, there was a typo in the way I phrased the original question. Here
is the update:

I have a file called Book1. In cell C1 on Sheet2 there is an equation that
reads:

=Sheet1!A1+10

I have another file called Book2. When I copy the above equation into cell
C1 on Sheet2 of Book2, it pastes in the following:

=[Book1]Sheet1!A1+10

So Excel wants to make a reference back to Sheet1!A1 in the original file.

I want to copy the equation over to the new book, but just have it make
reference to Sheet1!A1 in the new book, not back to the original file. It
seems like the reference to the sheet is absolute by default, but I want it
to be relative.

Is there a way to get Excel to copy a sheet reference from one file to
another without making reference back to the original file?

Thanks for any help.