It sounds like the longer code does what your shorter code already does.

Is that not the case?

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
This converts the name of the range into the range behind it. Hence I
still
get the entire range in the formula. For example when I run it I get
=Sheet1!$A$1:$G$1 in B1. I am running this in my Personal Workbook do I
changed all your references to Thisworkbook to ActiveWorkbook.

EM

Sub ReplaceNamesWithRefs()
' Loop counter
Dim i As Integer
Dim szWhat As String
Dim szReplace As String
Dim wks As Worksheet
Dim rRng As Range

i = 1

'On Error Resume Next
' Loop through all names in the workbook
Dim nm As Name
For Each nm In ActiveWorkbook.Names
'Store each name object in a variable
szWhat = ActiveWorkbook.Names(i).Name
'In case we find cells that match the defined name's name
For Each rRng In Cells.SpecialCells(xlCellTypeConstants)
If rRng.Value = szWhat Then GoTo NameMatch
Next rRng
'Store each names RefersTo value in a variable that trims the "="
sign
szReplace = Replace(ActiveWorkbook.Names(i).RefersTo, "=", Empty)

'================================================= ==================
' Loop through the sheets collection, replacing the names
with
' the actual range references
For Each wks In ActiveWorkbook.Worksheets
wks.Cells.SpecialCells(xlCellTypeFormulas) _
.Replace szWhat, szReplace, xlPart, , True
Next wks

'================================================= ===================
NameMatch:
i = i + 1
Next nm
End Sub

"JLXL via OfficeKB.com" wrote:

In a standard module: Not perfect, so have a backup:

'================================================
Option Explicit

Sub ReplaceNamesWithRefs()
' Loop counter
Dim i As Integer
Dim szWhat As String
Dim szReplace As String
Dim wks As Worksheet
Dim rRng As Range

i = 1

On Error Resume Next
' Loop through all names in the workbook
Dim nm As Name
For Each nm In ThisWorkbook.Names


' Store each name object in a variable
szWhat = ThisWorkbook.Names(i).Name


' In case we find cells that match the defined name's
name
For Each rRng In Cells.SpecialCells(xlCellTypeConstants)
If rRng.Value = szWhat Then Goto NameMatch
Next rRng


' Store each names RefersTo value in a variable that
trims the
"=" sign
szReplace = Replace(ThisWorkbook.Names(i).RefersTo, "=", Empty)


'
================================================== =================
' Loop through the sheets collection, replacing the names
with
' the actual range references
For Each wks In ThisWorkbook.Worksheets

wks.Cells.SpecialCells(xlCellTypeFormulas) _
.Replace szWhat, szReplace, xlPart, , True

Next wks
'
================================================== =================


NameMatch:

i = i + 1

Next nm

End Sub