VBA Worksheet Sub Create Named Range in Another Worksheet

Question

I have a private sub that needs to create named ranges within another worksheet. It needs to stay a worksheet function, as it is a Worksheet_Change sub. I have successfully been able to set a range variable equal to a range on another sheet with this line:

Set rng2 = Sheets("Lists").Range(Sheets("Lists").Cells(2, Col), Sheets("Lists").Cells(Unique, Col))

However, when I put rng2 into the other portion of my code, it simply refers to the correct range within the Active Sheet.

Here is what I have tried:

ActiveWorkbook.Names.Add Name:="Level" & Col, RefersTo:= _
    "= " & Sheets("Lists").Range(Sheets("Lists").Cells(2, Col), Sheets("Lists").Cells(Unique, Col)).Address & ""

and:

ActiveWorkbook.Names.Add Name:="Level" & Col, RefersTo:= _
    "=" & rng2.Address & ""

The bottom function works when it is within a module stored inside the workbook as a whole, but again, does not work within a worksheet sub. I have also tried Sheets("Lists").rng2.Address in the bottom attempt.

Answer 1

To have the address include the sheet's name, you have to set the external parameter:

rng2.address(external:=True)

Answer 2

Your RefersTo string needs to be something like "=Lists!A1". So all it's missing is the reference to the lists worksheet.

Try something like this:

Dim wsLists As Worksheet
Set wsLists = ThisWorkbook.Worksheets("Lists")

With wsLists
    Set rng2 = .Range(.Cells(2, Col), .Cells(Unique, Col))
    ThisWorkbook.Names.Add Name:="Level" & Col, RefersTo:="=" & rng2.Address(external:=True)
End With