links

[rodney_malod]

i have a spreadsheet with links, i wish to remove these links but retain the infomation.

how can this be done?

[johnske]

If you also want to lose the worksheet formulas...

[vba]Option Explicit
'
Sub DeleteLinksAndFormulas()
With Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
.Hyperlinks.Delete
End With
End Sub[/vba]

If you want to keep the other worksheet formulas, use...
[vba]Option Explicit
'
Sub DeleteLinksOnly()
Dim Cell As Range, FirstAddress As String, Temp As String
'
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
Set Cell = .Find("=*!", LookIn:=xlFormulas, LookAt:=xlPart)
On Error GoTo Finish
FirstAddress = Cell.Address
Do
Temp = Cell
Cell.ClearContents
Cell = Temp
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
.Hyperlinks.Delete
End With
Application.ScreenUpdating = True
End Sub[/vba]