Dim lastrow As Long
lastrow = Sheets("template").Range("B" & Rows.Count).End(xlUp).Row
'
Application.Names.Add "DVList", RefersTo:="='template'!$B$3:$B$" & lastrow
'
With Sheet8
    With .Range("B2").Validation
        .Delete
        .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="=DVList"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End With

Regards, TMS