Copy All Named Ranges To Another Workbook With VBA

Question

Please read the following post first; Copy all named ranges to another workbook. I am trying the same, the answers on this page and information on other sites has gotten me nowhere.

I have adapted the code a little. With this code i get the following error; Error 1004 "Application-defined or Object-defined error". Help is greatly appreciated. The error shows on the following line:'ThisWorkbook.Names.Add Name:=x.Name, RefersTo:=x.Value'

Sub CopyNames()
'Gedefineerde namen kopiëren naar andere werkmap.
    Dim x As Name
    Dim wbDatabase As Workbook
    Set wbDatabase = Workbooks("Database.xlsx")
    
    For Each x In wbDatabase.Names
        ThisWorkbook.Names.Add Name:=x.Name, RefersTo:=x.Value
    Next x
End Sub

Answer 1

I have found the solution to my question by accident. A hidden name was created by Excel, named _FilterDatabase. I learned on the following site (https://www.reddit.com/r/excel/comments/78t8mw/what_function_do_the_automatically_generated/) that this name is created whenever you filter something in the workbook. Since _FilterDatabase doesn't start with a letter it gives the error in the CopyNames code. Since I often use filters, deleting the name wouldn't help me in the long run. Excel will create the name again when I use a filter. I found a solution for this on the following site; https://www.mrexcel.com/board/threads/excel-keeps-creating-a-hidden-_xlfn-iferror-name-why.1081695/ .

---

First I used the following macro in my Database-Workbook to unhide all the defined names:

Sub ShowAllNames()
   Dim x As Name
   For Each x In ActiveWorkbook.Names
       x.Visible = True
   Next x
End Sub

I immediatly saw the _FilterDatabase name at the top of the list after unhiding all the defined names. Then I added the 'If Not x.Name Like "_Filter*" Then'-line to my CopyNames-code in my other Workbook. By adding this line to the code, the code will skip all names that start with _Filter. Here is the full code;

Sub CopyNames()
'Gedefineerde namen kopiëren naar andere werkmap.
    Dim x As Name
    Dim wbDatabase As Workbook
    Set wbDatabase = Workbooks("Database.xlsx")
    
    For Each x In wbDatabase.Names
        If Not x.Name Like "_Filter*" Then
          ThisWorkbook.Names.Add Name:=x.Name, RefersTo:=x.Value
        End If
    Next x
End Sub