Assign a Range to Named Range in VBA

[mnuttall87]

Quick question for any VBA gurus out there (I can't seem to find out what is going on here).


I have a short line of code:

' Cells.Find(What:="Partnership", After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Select

Range(Selection, Selection.End(xlDown)).Select

strName = Selection.Address

ActiveWorkbook.Names.Add Name:="DistPship", RefersTo:="Distributions!" & strName'


My problem is that when it creates the named range, the name manager has the value equal to the Distributions!A1:A1882 (this is a variable number that will change, hence passing it through VBA).


Any ideas on how to make the value update?

 

[mnuttall87]

I guess the problem is that it returns the Distributions! with the quotes around it, but if you remove them it says to create a variable....but I don't really need that as a variable as it is just the workbook name.

 

[SirJB7]

Hi, mnuttall87!


Try adding an equal sign "=" (unquoted) before the worksheet name:

RefersTo:="=Distributions!" & strName


Regards!