Hi,

I need to have a vba routine to get rid of external links. If someone copy
and paste sheet data between workbooks, there will often become external
references in the 'paste to workbook' pointing to the "copy from workbook'.
Before closing a workbook, I would like to check if there is any of these
links.

The best I found so far is the following from Steve Arbaugh, with minor
changes. However, it just delete the link completely. I just want to get rid
of the external part of the reference - the internal cell reference should
still be valid.

Can someone help me?

/Kindest regards

Function FindBookExtRefs()

Application.ScreenUpdating = False
Application.EnableEvents = False
Dim objRange As Range
Dim i As Integer
Dim j As Long


For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Activate
Set objRange = ActiveSheet.UsedRange
For i = 1 To objRange.Columns.Count
For j = 1 To objRange.Rows.Count
objRange(j, i).Select
If InStr(objRange(j, i).Formula, "[") 0 Then
'there's an external reference
If MsgBox("There's an external reference to: " _
& objRange(j, i).Formula & _
" in cell: " & _
ActiveSheet.Name & " " & _
ActiveCell.Address & _
"; do you want to delete it?", _
vbYesNo) = vbYes Then _
objRange(j, i).Formula = Null
End If
Next j
Next i
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
End Function