Easiest way to recreate all named ranges / copy them to a new file

[d0rian]

Over a couple years, I think a large file that I've created has become corrupt or bloated, and I'm recreating it from scratch in a new .xlsm document. I don't want any vestiges of the old file to remain (since I want the 'cleanest' new file possible).

1) I first Find/Replaced all of the '=' symbols to a pipe symbol ('|') (so that formulas I pasted to the new file didn't refer to the old file) then copy/paste-as-texted the contents from each of the 9 worksheets to an identically-named worksheet I created in the new file. I then re-replaced all of the pipe (|) symbols with equals's (=) in the new file.

2) I then used the Format Painter tool to copy over the formats (although I first deleted the conditional formatting from the source file, which I think I'll have to recreate by hand, since when I tried to copy it over, the CF formulas referred to the OLD file, which is obviously not good.)

But now I'm left with a bunch of formulas that refer to Named Ranges that don't exist in the new file, resulting in a bunch of #NAME ? errors. I have several hundred of them that I need to bring over from the old file. What's the quickest way to do that? I couldn't find any 'export' function or anything like that...

EDIT (any tips for bringing over the conditional formatting stuff I'd be grateful for as well...)

 

[AlphaFrog]

There's a free Name Manager add-in that will make it easy...

Name Manager: Range Names Made Easy

 

[d0rian]

Thanks, will check it out. Before leaving my OP, though -- I found this page, which had simple enough code (pasted below) that seemed to do what I want, but every time I run it, it generates an error that kills the copy process, and I go to the target workbook and look in the Name Manager there, and see the image below. What appears to be happening is that it's copying a single one of the dozens of Ranges from the source workbook and then crashing...but what's weird is that there is no "_FilterDatabase" named range in the source workbook. I have no idea what it is...I certainly didn't create it. And strangely, when I go to the source workbook and look in the Named Range manager, it's not even listed there. And when I highlight the A4:AE443 range in the DYN worksheet, it doesn't recognize it (in the top-left of the Excel window) as being a named range of any kind.

This is probably just minor but in my effort to troubleshoot everything that's gone wrong w/ my source workbook, I want to understand where this "_FilterDatabase" named range even came from (and if there are perhaps hundreds of others lurking beneath the surface or something...)

[COLOR=#000000][FONT=Menlo]Sub CopyNames()[/FONT][/COLOR]    Dim Source As Workbook
    Dim Target As Workbook
    Dim n As Name

    Set Source = ActiveWorkbook
    Set Target = Workbooks("Book2.xlsx")

    For Each n In Source.Names
        Target.Names.Add Name:=n.Name, RefersTo:=n.Value
    Next [COLOR=#000000][FONT=Menlo]End Sub[/FONT][/COLOR]

 

[AlphaFrog]

_FilterDatabase is a hidden name that is created after you filter a range using the Excel Advanced Filter, or after you apply an AutoFilter to a range (the hidden name is retained for the range even if you subsequently remove the AutoFilter arrows). Additionally, you can you use _FilterDatabase as a range name in VBA.

    For Each n In Source.Names
        If Not n.Name Like "_*" then Target.Names.Add Name:=n.Name, RefersTo:=n.Value
    Next End Sub