From: Greg on 11 May 2010 14:30 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
From: Chip Pearson on 11 May 2010 15:09 No, you cannot restore a name back to some previous reference. Once you change the location to which a name refers, you can't automatically go back to some prior value. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998  2010 Pearson Software Consulting, LLC www.cpearson.com On Tue, 11 May 2010 11:30:02 0700, Greg <Greg(a)discussions.microsoft.com> 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
From: Gary''s Student on 11 May 2010 15:22 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
From: Greg on 11 May 2010 17:38 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

Pages: 1 Prev: If Vlookup Question Next: Sorting multiple columns that have similar data 