I've changed the below as you specified...but now its asking to be manually pointed. Which I'm guessing is good and we're heading on the right path. 

This is going to have to be done trial and error.
If InStr(link, "Mainstay Master.xlsx") Then _

looks for .xlsx, shouldn't it be looking for xlsm?
Or more completely: Mainstay Master Template.xlsm ?


I've changed from thisworkbook to B.names.

but the code now breaks on this line... with application or object defined error. Is this because of the Sitenames reference? 

    Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"
I suspect, but not sure, that your:



	
ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng




should be:



	
b.Names.Add Name:="Sitenames", RefersTo:=Rng




[/QUOTE]