MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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
'Reset count Dim
x = x - 1
'Activate next window
For x = x To 0 Step -1
ActiveWorkbook.Names.Add Name:=Names(x, 0), RefersTo:=Names(x, 1)

End Sub