Here's something to get you started...

Sub NamesLoop()
Dim N As Name
Dim strIN As String
Dim strOUT As String

   For Each N In ActiveWorkbook.Names
       strIN = N.RefersTo

'----------------------------------------------------------
'   strIN will be in a form something like -> "=Sheet1!R1C1"

'   Here you need to parse strIN, make your change and put _
   the result into strOUT
'----------------------------------------------------------
   
       N.RefersToR1C1 = strOUT
   Next N
End Sub

HTH    NickH