select cell after sorting

[Steve]

Private Sub Worksheet_Change(ByVal Target As Range)

Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("a1").Select



End Sub

Easy enough. I wanted the spread sheet to sort the column by ascending once
the user enters new data. I also want the new data to be the active selection
after it has been sorted. Does anyone know how I can do this? Thanks.

 

[Bernie Deitrick]

Steve,

As long as the new value is unique: otherwise, this will pick up the first instance.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myVal As Variant
myVal = Target.Value

Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A:A").Find(myVal).Select

End Sub

Of course, you can get around that limitation, but it requires more work....

HTH,
Bernie
MS Excel MVP