VBA Worksheet Sub Create named range in another sheet

Question

I have a private part that should create named ranges in another sheet. It should remain a function of the worksheet, as this is subtext Worksheet_Change. I managed to set a range variable equal to the 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 rng2in another part of my code, it just refers to the correct range in the active sheet.

Here is what I 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 inside the module stored inside the book as a whole, but again, it does not work inside the subdirectory of the sheet. I also tried Sheets("Lists").rng2.Addressin the bottom attempt.

Answer 1

, external:

rng2.address(external:=True)

Answer 2

RefersTo - "=Lists!A1". , , , .

- :

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