Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
reset defined ranges
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
reset defined ranges
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 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
reset defined ranges
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
reset defined ranges
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing to defined ranges | Excel Worksheet Functions | |||
Create new list with defined ranges | Excel Worksheet Functions | |||
Create new list with defined ranges | Excel Worksheet Functions | |||
dynamic defined ranges | Excel Worksheet Functions | |||
Named ranges reset. | Excel Worksheet Functions |