VBA Request - Break External Links Leave Formulas and Values

[aalcorn]

Hi, I am trying to modify this VBA I found on the forum that is several years old.

Sub Test()
Const LinkName As String = "Book1.xlsx"
Dim CalcMode
Dim ws As Worksheet
Dim WorkRange As Range
Dim Cell As Range
Dim FormulaParts As Variant
Dim i As Integer
Dim NewFormula As String
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Resume Next
Set ws = ActiveSheet
Set WorkRange = ws.Cells.SpecialCells(xlCellTypeFormulas)
If Err <> 0 Then Exit Sub
For Each Cell In WorkRange
With Cell
If InStr(1, .Formula, "[" & LinkName, 1) Then
FormulaParts = Split(Replace(.Formula, "=", ""), "+")
For i = LBound(FormulaParts) To UBound(FormulaParts)
If InStr(1, FormulaParts(i), "[" & LinkName, 1) Then
FormulaParts(i) = 0
End If
Next i
NewFormula = Join(FormulaParts, "+")
NewFormula = Replace(NewFormula, "0+", "")
NewFormula = Replace(NewFormula, "+0", "")
If Len(NewFormula) > 1 Then
.Formula = "=" & NewFormula
Else
.ClearContents
End If
End If
End With
Next Cell
Application.Calculation = CalcMode
End Sub


I need to create the same effect of editing a formula [f2] and calculating the value [f9] of a specific sheet reference while keeping the rest of the formula intact. I moved the sheet to an external file to match the VBA I found.

I am new to VBA but can see there should be a way to modify this to calculate the value instead of just removing the reference. Editing each individual formula would take a very long time so I am looking for a worksheet solution.

Thanks