does not work. I think there is some error with the below loop. Excel is
trying to open some file to update values. The ranges do not have external
references though

For Each r In Cells.SpecialCells(xlCellTypeFormulas)
r.Formula = Replace(r.Formula, nn, naddy)
Next




"Gary''s Student" wrote:

This little macro will replace each Named Range in each formula with the
orgiinal range:

Sub FixNames()
Dim n As Name, nn As String, naddy As String
Dim r As Range
For Each n In ActiveWorkbook.Names
nn = n.Name
naddy = Right(n.RefersTo, Len(n.RefersTo) - 1)
For Each r In Cells.SpecialCells(xlCellTypeFormulas)
r.Formula = Replace(r.Formula, nn, naddy)
Next
Next
End Sub

So if alpha is:
A1:A3
and beta is:
B1:B3

formulas like:
=SUM(alpha)
will become:
=SUM(Sheet1!$A$1:$A$3)
--
Gary''s Student - gsnu201002


"Greg" wrote:

hi,

is there a way to reset (not just delete) the named ranges back to the
original references so the formulas would still work?

Using Excel 2007
thank you

--
______
Regards,
Greg