How select a variable range to paste in VBA when there are blank cells in between

Question

I want to paste a formula of VLOOKUP in my whole column but the last cell changes everyday. How to select the variable last cell of Column "S" according to the last cell in column "A".

Sub Macro9()
'
' Macro9 Macro
'

'
Range("S2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
Range("S2:S38").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("S2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
End Sub