Copy range names from a workbook to another workbook
Posted by Thierry Verviers on September 08, 2000 12:10 PM
I search a manner in vba to copy all range names
from a particular zone in a workbook one in
another workbook two. If the range name exist already,
the new name should replace the old name.
Posted by Ivan Moala on September 09, 0100 5:38 PM
Try this;
Note: it will overwrite any old names with the
new names and ranges automatically anyway.
Sub GetNamedRange_CopyToNextBook()
Dim Nm As Object
Dim Names()
Dim x As Integer
ReDim Names(ActiveWorkbook.Names.Count, 1)
' Loop through each Name in the ActiveWorkbook.
For Each Nm In ActiveWorkbook.Names
Names(x, 0) = Nm.Name 'Defined Name
Names(x, 1) = Nm 'Refers to range
x = x + 1
Next
'Reset count Dim
x = x - 1
'Activate next window
ActiveWindow.ActivateNext
For x = x To 0 Step -1
ActiveWorkbook.Names.Add Name:=Names(x, 0), RefersTo:=Names(x, 1)
Next
End Sub
HTH
Ivan
