Naming ranges as a copy of another sheet

A

Andy Chan

Dear all,

There are two sheets X and Y in my workbook. On A, there are hundreds of
ranges named locally (i.e. names are like "X!students"). How to write a
macro to name the respective areas in Y with the same local name? For
example, if X!$A$1:$B$4 is named as "X!students", then I want Y!$A$1:$B$4 to
be named as "Y!students".

Thanks in advance.

Best Regards,
Andy
 
D

Dave Peterson

Something like:

Option Explicit
Sub testme01()

Dim wksMstr As Worksheet
Dim wksOther As Worksheet
Dim nm As Name
Dim testRng As Range
Dim ExclamPos As Long

Set wksMstr = Worksheets("x z")
Set wksOther = Worksheets("y z")

For Each nm In wksMstr.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = nm.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
ExclamPos = InStr(1, nm.Name, "!", vbTextCompare)
If ExclamPos > 0 Then
With wksOther
.Names.Add _
Name:="'" & .Name & "'" & Mid(nm.Name, ExclamPos),
_
RefersTo:=.Range(testRng.Address)
End With
End If
End If
Next nm
End Sub


I'd get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp
 
Top