This is a generalised code for Excel VBA Data Validation. It can be used across sheets in a workbook.

Sub createValidation(rngValidation As Range, rngReference As Range)

    rngValidation.Validation.Delete

    rngValidation.ClearContents

    Dim strRefRange As String

    strRefRange = "=indirect(" & """" & "'" & rngReference.Parent.Name & "'!" & rngReference.Address & """" & ")"

    With rngValidation.Validation

         .Delete

         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

         xlBetween, Formula1:=strRefRange

         .IgnoreBlank = True

         .InCellDropdown = True

         .InputTitle = ""

         .ErrorTitle = ""

         .InputMessage = ""

         .ErrorMessage = ""

         .ShowInput = True

         .ShowError = True

     End With

End Sub