Hi team,
I have searched the interweb looking for a TM1 break link macro and found the following:
What I am seeing is that this won't break the links of cells that reference other cells. So if I had a cell with =(DBRW....)-E10 (this formula stays)
Any help is appreciated.
Thank you.
I have searched the interweb looking for a TM1 break link macro and found the following:
Code:
Private Function DeleteLinksInWS(ConfirmReplace As Boolean, _ ws As Worksheet) As Boolean
'replace formulas with their values
Dim cl As Range, cFormula As String, i As Integer
DeleteLinksInWS = True
If ws Is Nothing Then Exit Function
Application.StatusBar = "Deleting external formula references in " & _
ws.Name & "..."
ws.Activate
For Each cl In ws.UsedRange
cFormula = cl.Formula
If Len(cFormula) > 0 Then
If Left$(cFormula, 5) = "=SUBN" Or Left$(cFormula, 3) = "=DB" Or Left$(cFormula, 5) = "=VIEW" Then
If Not ConfirmReplace Then
cl.Formula = cl.Value
Else
Application.ScreenUpdating = True
cl.Select
i = MsgBox("Replace the formula with the value?", _
vbQuestion + vbYesNoCancel, _
"Replace external formula reference in " & _
cl.Address(False, False, xlA1) & _
" with the cell value?")
Application.ScreenUpdating = False
If i = vbCancel Then
DeleteLinksInWS = False
Exit Function
End If
If i = vbYes Then
On Error Resume Next
' in case the worksheet is protected
cl.Formula = cl.Value
On Error GoTo 0
End If
End If
End If
End If
Next cl
Set cl = Nothing
Application.StatusBar = False
End Function
What I am seeing is that this won't break the links of cells that reference other cells. So if I had a cell with =(DBRW....)-E10 (this formula stays)
Any help is appreciated.
Thank you.