Sub RemoveCurrentWorksheetReferencesFromFormulas()

Dim ws As Worksheet
Dim VisibleStatus As Variant
Dim ProtectStatus As Boolean
Dim ws_NameReplace As String
Dim ws_NameReplace2 As String
Dim dummyvariable As Variant

Dim NameSwap As String
NameSwap = ""


'Speed up calculations by switching calculation and screen updating off
Dim InitialCalc As Variant
InitialCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'Reset Find/Replace behaviour to look at sheet only (not workbook)
Set dummyvariable = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)

'Error handling - if there's a problem, skip to the next worksheet
On Error GoTo NextWorksheet

'Repeat the following code for each worksheet in the workbook
For Each ws In ActiveWorkbook.Sheets

    'Store whether worksheet is hidden or not, and unhide if necessary
    VisibleStatus = ws.Visible
    ws.Visible = xlSheetVisible
    ws.Activate
    
    'Store whether worksheet is protected or not, and unprotect if necessary
    ProtectStatus = ws.ProtectContents
    ws.Unprotect ""
    
    'Create variables to store the name of the worksheet in two different formula forms - with/without space
    ws_NameReplace = "'" & ws.Name & "'!"
    ws_NameReplace2 = ws.Name & "!"
    
    'Replace if sheet name has a space
    ws.Cells.Replace What:=ws_NameReplace, Replacement:=NameSwap, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    'Replace if sheet name does not have a space
    ws.Cells.Replace What:=ws_NameReplace2, Replacement:=NameSwap, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    
    'Rehide the worksheet if it's hidden
    ws.Visible = VisibleStatus
    
    'Reprotect the worksheet if it was protected previously
    If ProtectStatus = True Then
        ws.Protect
    End If

NextWorksheet:

Next ws

'Reset calculation status
Application.Calculation = InitialCalc
Application.ScreenUpdating = True


End Sub