Hi all,

I know that when you copy a sheet from a workbook to another workbook
references to the original cells are built. I have written a marco t
remove the references, but it looks stupid. Therefore, is that any wa
not to copy the reference to the new workbook?

Any help is appreciated

--
Message posted from http://www.ExcelForum.com

Why don't you show us your "stupid" macro? It might then be easier t
correct.

Another option is to copy the cells instead of copying the sheet

--
Message posted from http://www.ExcelForum.com

Here the stupid code comes:


Sub OFA_Remove_References()
Dim pos1 As Integer
Dim pos2 As Integer
Dim temp1 As String
Dim temp2 As String
Dim temp3 As String
Dim c As Range

Application.ScreenUpdating = False

For Each c In Selection
If c.Value < "" And InStr(1, c.Formula, "VLOOKUP"
vbTextCompare) 0 Then
pos1 = InStr(1, c.Formula, "'", vbTextCompare) 'First '
pos2 = InStr(pos1 + 1, c.Formula, "'", vbTextCompare
'Second '

If pos1 0 And pos2 0 Then
'remove the reference
temp1 = Left(c.Formula, pos1 - 1) 'get the first part
temp2 = Right(c.Formula, Len(c.Formula) - pos2 + 5
'get sheet name
temp3 = Right(c.Formula, Len(c.Formula) - pos2) 'ge
the later part

c.Formula = temp1 & Left(temp2, 4) & temp3
End If
End If
Next c

Application.ScreenUpdating = True
End Su

--
Message posted from http://www.ExcelForum.com

Something that have to remind my saviour:

1) the name of each sheet is of length 4
2) most of the cell in the sheet use vlookup() function and the whol
sheet can be considered as a template

Thanks

--
Message posted from http://www.ExcelForum.com

Any help

--
Message posted from http://www.ExcelForum.com

Can anyone give me an answer even it is not possible?

Thanks

--
Message posted from http://www.ExcelForum.com

How about an alternative?

http://groups.google.com/groups?thre...1C46%40msn.com

"kaon <" wrote:

Something that have to remind my saviour:

1) the name of each sheet is of length 4
2) most of the cell in the sheet use vlookup() function and the whole
sheet can be considered as a template

Thanks.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Thanks. It is a good idea to do it!

--
Message posted from http://www.ExcelForum.com