Data validation with source in different sheet

Question

Can you help me update this macro so the data validation list is in Sheet1 A2 but the data validation list source is Sheet2 last row in Column A with data?

Sub MacroExample()

Dim r As Range

With Worksheets("Sheet2")

Set r = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))

End With

With Selection.Validation

.Delete

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

xlBetween, Formula1:="=" & r.Address(True, True, xlA1, True)

End With

End Sub

Answer 1

Change

With Selection.Validation

to

With Worksheets("Sheet1").Range("A2").Validation

Though I'm not sure if you want a list, or just the last cell in column A of Sheet2 - "list source is Sheet2 last row in Column A with data"

If you only want the last filled cell in that column then you could just use a formula (assuming there are no blanks above the last value of the column):

=INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A))