VBA Name Range not Working

[bobo999]

Hey Guys. So I've created some code, part of which is designed to name a custom range.

Code is:

ThisWorkbook.Names.Add Name:="Model2", _
        RefersTo:=Worksheets("Sheet2").Range(range1)

Rather simple, generate the range name, and whatever worksheets we are on at the time, gets that named range. The code works great by itself, in the spreadsheet where the code was originally written.

HOWEVER, when I create a packed custom .xlam file, which I have added as an Add-in into excel, the code doesn't work. I execute the code step by step, get to the point where the code above executes, but it simply doesn't name the range.

Again, when running the code into the original spereadsheet where the code was written, it works, however when opening a new spreadsheet (with appropriate sheet names), the code executes, just doesn't do anything...

Thanks.

 

[Rick Rothstein]

Range names inside the Range object must be encased in quote marks just like you do with a cell's address. Try it this way...

ThisWorkbook.Names.Add Name:="Model2", RefersTo:=Worksheets("Sheet2").Range("range1")

 

[bobo999]

Sorry the code previous is me generating a range like so:

range1 = "E" & (rowbegin + 1) & ":" & "E" & (rowend + 1)
ThisWorkbook.Names.Add Name:="ImageType", _
    RefersTo:=Worksheets("Virtualization").Range(range1)

It only works without the quotations in that case...
Again it works fine in the original file where I wrote the code, however when I put it in an .xlam addin, and run in a new workbook, it simply doesn't do anything.

 

[bobo999]

Any thoughts on this ?

 

[bobo999]

To anyone who had a problem with this, the solution:
When running the code "thisworkbook", from the XLAM, excel is looking into the xlam as "thisworkbook". If you want to point the xlam to the workbook from where you are running the code, you must use "activeworkbook". Simple fix.