Chris,

The macro below will work with one major caveat: each name needs to be completely unique: if you
have one name that forms part of another name, then it may cause errors. For example, if you have
ChrisName, ChrisName1, and ChrisName2, then it won't work, because the string "ChrisName" appears in
its entirety in the next two names . But if you have just ChrisName1, and ChrisName2, then it will
work. Try it on a copy of your workbook...

HTH,
Bernie
MS Excel MVP

Sub RemoveNameReferences()
Dim myN As Name
Dim mySht As Worksheet
Dim myNstr As String

For Each mySht In ActiveWorkbook.Worksheets
For Each myN In ActiveWorkbook.Names
mySht.Cells.Replace What:=myN.Name, _
Replacement:=Mid(myN.RefersTo, 2), _
LookAt:=xlPart
Next myN
For Each myN In mySht.Names
mySht.Cells.Replace What:=myN.Name, _
Replacement:=Mid(myN.RefersTo, 2), _
LookAt:=xlPart
Next myN
For Each myN In mySht.Names
myNstr = Replace(myN.Name, IIf(InStr(1, mySht.Name, " ") 0, _
"'" & mySht.Name & "'!", mySht.Name & "!"), "")
MsgBox myNstr
mySht.Cells.Replace What:=myNstr, _
Replacement:=Mid(myN.RefersTo, 2), _
LookAt:=xlPart
Next myN
Next mySht
End Sub



"Chris" wrote in message
...
I have inherited a spreadsheet with over 5000 range names. Unfortunately,
these range names were not created intuitively and have little meaning to
what they refer to. I would like to have a version of this workbook with only
the cell range references in the formulas for ease of navigation. Is there
some fast way to convert all range names to cell references? I thank you in
advance for your help