Problem copying named ranges to another workbook

[awardblvr]

Excel for Mac 2011

Trying to follow the macro created in this post about copying named ranges:

http://excelribbon.tips.net/<wbr>T008811_Copying_Named_Ranges.<wbr>html

(Use a macro to copy a bunch of named ranges to a new workbook with the exact same data set, worksheet names.)

But in the line that should be

Set Target = Workbooks("Book2.xlsx")

I did:

Set Target = Workbooks("/Users/my_userid/Downloads/Oly%20SPC3%<wbr>20with%20Glak%20DRD.xlsm")

And it dies at that point.

I get "Run-time error '9': Subscript out of range"

And it shows the error by highlighting the line above.

I opened up terminal to verify my target is properly named and that it exists and did:

[my_userid-mbp:~] my_userid% ls /Users/my_userid/Downloads/<wbr>Olys%20SPC3%20with%<wbr>20Glak%20DRD.xlsm
/Users/my_userid/Downloads/<wbr>Oly%20SPC3%20with%<wbr>20Glak%20DRD.xlsm

The full macro is:

Sub CopyNames()
    Dim Source As Workbook
    Dim Target As Workbook
    Dim n As Name

    Set Source = ActiveWorkbook
    Set Target = Workbooks("[FONT=monospace]/Users/[FONT=monospace]my_userid[/FONT]/Downloads/<wbr>Oly%20SPC3%20with%<wbr>20Glak%20DRD.xlsm[/FONT]")

    For Each n In Source.Names
        Target.Names.Add Name:=n.Name, RefersTo:=n.Value
    Next
End Sub

Any suggestions?

 

[Tom Urtis]

This is just a guess, but if it were me, I'd start by making sure I'm telling VBA the workbook name the way VBA sees it, not necessarily the way your terminal refers to it.

Open the target workbook.
Go into the VBE (Alt+F11).
Hit Ctrl+G to activate the Immediate window.
Type in...
? activeworkbook.fullname
...and then press Enter.
See if the name produced by that action is exactly (exactly means exactly) what's in your code.

I realize this is an elementary first step but it's usually a good idea with the 9 error to first make sure the basic info such as a file name is being properly referred to.

 

[awardblvr]

To validate I was using the right destination file, I copied the full posix compliant path to Workbooks(""). I also opened up the target file and use the "immediate window" and ran


? activeworkbook.fullname

and got
Macintosh HD:Users:my_userid:Documents:Oly:OlyJunk.xlsm

and pasted that into the Workbooks() function.. Same result.