Creating a named range from cell values

[david porter cpl]

I need a solution on how to create a named range based upon cell values. The cell values are variable, and the sheets on which the named ranges need to be created are hidden from the end user, hence the need to automate it. I've had some success with the actual name of the range as per a cells value, but I'm struggling to set a cell value as the array of cells in which to create the range itself. Any help would be greatly appreciated. Many thanks.

 

[offthelip]

try this:

Sub test()
Dim rst As String
Dim Sht As String
Dim nam As String

Dim tt As Range
Sht = Range("c2")
rst = Range("c3")
nam = Range("c4")
Set tt = Worksheets(Sht).Range(rst)
    ActiveWorkbook.Names.Add _
        Name:=nam, _
        RefersTo:=tt
        
End Sub