Removing a link

[Rickalty]

I have a multi-sheet workbook in Excel. I copied a worksheet from another workbook into it, then deleted the other workbook. The sheet I copied over had a link to data on another page of the - now deleted - workbook. Now I can't get the link to refer to data in the current workbook. I tried going to "Edit" : "Links" and changing the source to be the current workbook, but it didn't work. How can I get rid of that external link completely ?

Thanks in advance.

 

[VoG]

Have a look at this

http://j-walk.com/ss/excel/usertips/tip054.htm

 

[NateO]

Howdy Richard, with some limited testing, I used the following code to remove the link portion of the formula, leaving the worksheet & cell reference of worksheet formulae:

<pre>
Sub Links()
Dim ws As Worksheet, cl As Range, frm As String, FrmCls As Range
Dim rplc As String, n As Integer, i As Integer
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set FrmCls = ws.[a1].SpecialCells(xlFormulas)
If FrmCls Is Nothing Then GoTo 1
For Each cl In FrmCls
frm = cl.Formula
If InStr(frm, "]") And InStr(frm, ":") Then
i = InStr(frm, "]")
n = InStr(frm, ":")
rplc = Mid(frm, n - 1, i - (n - 2))
cl.Replace What:=rplc, Replacement:=""
End If
Next cl
1:
Set FrmCls = Nothing
Next ws
Application.DisplayAlerts = True
End Sub</pre>

Otherwise one may be looking at pasting as values or the MS file Barrie mentions at the following link:

http://www.mrexcel.com/board/viewtopic.php?topic=8267&forum=2

Never tried this file before, not sure what they mean when they say "delete."

 

[Rickalty]

Thanks for the replies, guys. Doing the "Insert" : "Name" : "Define" thing solved my problem.... the "phantom link" wasn't in a data containing cell - it was in the signature block where every page of my report says "Original Signature on File". On the one page that I'd copied over the link was to the defunct workbook. Thanks again.