Copy sheets from A to B without reference

[kaon]

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

 

[MSP77079]

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

 

[kaon]

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

 

[kaon]

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

 

[kaon]

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

Thanks

 

[Dave Peterson]

How about an alternative?

http://groups.google.com/[email protected]

 

[kaon]

Thanks. It is a good idea to do it!