Excel Error: "This Type of Reference Cannot Be Used in a Data Validation Formula"

Try this:

Option Explicit
Option Base 1

' You can access the Lists from all Sheets of your Workbook!
Public lstDraftRefs As Validation
Public lstFinalRefs As Validation

Public Sub CreateValidation(ByVal SH As String, _
                            ByRef R As Range, ByRef C As Range, _
                            ByRef X As Validation, ByVal InputMessage As String)
Dim N As Long
Dim V As Variant
Dim I As Integer
Dim J As Integer

   N = 0
   For Each V In C
      I = C.Row
      J = C.Column
   
      N = N + 1
   Next
   If (N <> 1) Then Exit Sub
   
   ActiveSheet.Cells(I, J).Select
   With Selection.Validation
      .Delete
   
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="=" & SH & "!" & R.Address
      
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = InputMessage
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
   End With
   Set X = Selection.Validation
      
End Sub

Public Sub X()
Dim R As Range
Dim C As Range

   ThisWorkbook.Worksheets(1).Activate
   Set R = ThisWorkbook.ActiveSheet.Range("$A$2:$A$10")
   ThisWorkbook.Worksheets(2).Activate
   Set C = ThisWorkbook.ActiveSheet.Range("$A$1")
   Call CreateValidation("Sheet1", R, C, lstDraftRefs, "Input lstDraftRefs")
   
   ThisWorkbook.Worksheets(1).Activate
   Set R = ThisWorkbook.ActiveSheet.Range("$B$2:$B$10")
   ThisWorkbook.Worksheets(3).Activate
   Set C = ThisWorkbook.ActiveSheet.Range("$A$1")
   Call CreateValidation("Sheet1", R, C, lstFinalRefs, "Input lstFinalRefs")

End Sub

Have fun.

Do not change the "Set"s to "Let"s or "ByRef"s to "ByVal"s.