defined names

[ehsas]

How can i copy the defined names from one workbook and paste the same into another workbook.Regards

 

[Andrew Poulsom]

Should be myNme1 as declared with Dim above.

Seems Nate does not use Option Explicit.

 

[ehsas]

I tried but it seem there is error in macro.Please hlep.Regards,

 

[Andrew Poulsom]

What is the error?

 

[ehsas]

myNme = Range(nm).Address(False, False)

It was highliting the above statement.As you said i changed the mynme to mynme1 still it highliting the statement.I deleted the statement other statement is also highliting.You can try and see.Regards,

 

[Andrew Poulsom]

I tested it with:

myNme1 = Range(nm).Address(False, False)

and I get no errors.

What is the error in Excel's dialog box when the code fails?

 

[ehsas]

i am getting following error message:-

runtime error 1004

method range of object global failed.

 

[Andrew Poulsom]

It doen't like the Address property with dynamic ranges like yours.

This seems to work:

Sub Cpy_NmRngs()
    Dim nm As Name
    Dim myNme1 As String
    Dim myNme2 As String
    Dim NewBook As String
    NewBook = "Book2.xls" 'change Book2 to the name of target workbook
    For Each nm In ThisWorkbook.Names
        myNme1 = nm.Name
        myNme2 = nm.RefersTo
        Workbooks(NewBook).Names.Add Name:=myNme1, RefersTo:=myNme2
    Next nm
End Sub

 

[ehsas]

Workbooks(NewBook).Names.Add Name:=myNme1, RefersTo:=myNme2

it stops at above statement and giving me error message as follow:-

runtime error 9
subscript out of range.

Regards,

 

[Andrew Poulsom]

There is a comment in the code:

"change Book2 to the name of target workbook"

Did you?

 

[ehsas]

yes i change but still it giving me same error messege.I have copied the macro to my source file. and run the macro.Let me tell you that I have more than one defined names in my workbook.I hope it not make any difference.Regards,