Copy all range names from one workbook to another

[Farmland]

Hi all,

I want to copy the names defined in one workbook "book1.xls" to tab "TD" in another workbook "Book2.xls".

The Source sheet used contains more than 1000 defined names. I tried a few codes I found online, but non of them worked.

I would really appreciate any advise you can give.

Thank you!
Sue

 

[igold]

Hi Sue, welcome to the MrExcel Forum.
How about this code placed in book1.xls. The code will place the results Book2 on Worksheets("TD") starting in Cell A1. If you don't want to start there, please change where indicated. Book2 must be open already.

Sub findNames()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim i As Long, arr
   
    ReDim arr(1 To wb.Names.Count, 1 To 2)
    For i = 1 To wb.Names.Count
       arr(i, 1) = wb.Names(i).Name
       arr(i, 2) = "'" & wb.Names(i).RefersTo
    Next
    Workbooks("Book2.xls").Worksheets("TD").Range("A1").Resize(UBound(arr), 2) = arr   'Change Range("A1") to your requirement

End Sub

 

[Farmland]

Thank you so much igold for your help. I really appreciate it.

 

[igold]

You're welcome, I was happy to help. Thanks for the feedback!