coltaalex
07-29-2010, 12:57 PM
i have a workbook with 25 sheets, and each sheet has hundreds of formulas,
i tried the flowing code, but is is running forever,
i any other way,
Sub Remove_External_Links_in_Cells()
Dim LinkCell
Dim FormulaCells As Range
Dim SheetsInWorkbook As Object
For Each SheetsInWorkbook In Sheets
SheetsInWorkbook.Activate
'Error trapping if no formulacells found
On Error Resume Next
'Select only formula cells
Set FormulaCells = Cells.SpecialCells(xlFormulas)
'Loop every formulacell in sheet
For Each LinkCell In FormulaCells
'If you want paste linked value as "normal value"
If InStr(1, LinkCell.Value, ".xls]") = 0 Then
LinkCell.Value = LinkCell
End If
Next
Next
End Sub
i tried the flowing code, but is is running forever,
i any other way,
Sub Remove_External_Links_in_Cells()
Dim LinkCell
Dim FormulaCells As Range
Dim SheetsInWorkbook As Object
For Each SheetsInWorkbook In Sheets
SheetsInWorkbook.Activate
'Error trapping if no formulacells found
On Error Resume Next
'Select only formula cells
Set FormulaCells = Cells.SpecialCells(xlFormulas)
'Loop every formulacell in sheet
For Each LinkCell In FormulaCells
'If you want paste linked value as "normal value"
If InStr(1, LinkCell.Value, ".xls]") = 0 Then
LinkCell.Value = LinkCell
End If
Next
Next
End Sub