how do you auto sort based on certain column

Maybe:

Sub Button1_Click()
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SortFields.Clear
    .SortFields.Add _
        Key:=Range("AY:AY"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
    .SetRange Range("AY:AY")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

Regards

it would be columns A2:AZ2 based on AY2.

Change this:

.SetRange Range("AY:AY")

to this:

.SetRange Range("A:AZ")


Regards