Option Explicit 
 
Public Sub ReplaceNamesWithRefs() 
    Dim rng             As Excel.Range 
    Dim wks             As Excel.Worksheet 
    Dim nme             As Name 
    Dim nCt             As Integer 
    Dim szWkbName       As String 
    Dim szSwitchString  As String 
     
    nCt = 1 
     
    With Application 
        .EnableEvents = False 
         
        On Error Resume Next 
         'Loop through all names in the workbook
        For Each nme In ThisWorkbook.Names 
             
             'Store each name object in a variable
            szWkbName = ThisWorkbook.Names(nCt).Name 
            .StatusBar = "Checking Defined Name " & CStr(szWkbName) 
             
             'Store each names RefersTo value in a variable that trims the (=)&($)
            szSwitchString = Replace$(ThisWorkbook.Names(nCt).RefersTo, "=", "") 
            szSwitchString = Replace$(szSwitchString, "$", "") 
             
             '===================================================================
             'Loop through the sheets collection, replacing the names with
             'the actual range references
            For Each wks In ThisWorkbook.Worksheets 
                 
                With wks.Cells.SpecialCells(xlCellTypeFormulas) 
                    .Replace$ szWkbName, szSwitchString, xlPart, , True 
                    .Replace$ wks.Name & "!", "", xlPart, , True 
                End With 
                 
            Next wks 
             '===================================================================
             
            nCt = nCt + 1 
             
        Next nme 
         
         'This code portion was created by Jim Rech
        For Each wks In ThisWorkbook.Worksheets 
            With wks 
                .TransitionFormEntry = True 
                For Each rng In .Cells.SpecialCells(xlFormulas) 
                    rng.Formula = rng.Formula 
                Next rng 
                .TransitionFormEntry = False 
            End With 
        Next wks 
         
        .EnableEvents = True 
        .StatusBar = False 
    End With 
End Sub