Need VBA to sort column after entering data

[David Schaleger]

I need VBA code for Excel 2007 to sort column A (includes header row) in a formated table after I enter data into any cell in column A.

This will be used in a few tables within this workbook, the tables being products, vendors, and payment types. I pull the data from these tables into a purchases table. I have to remember to sort the colums A to Z each time I enter new data, otherwise when I Alt+DownArrow in the purchases table to bring up the corresponding list, it's only alphabetized since the list time I remembered to sort A to Z. Would really like to automate this A to Z sorting procedure.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    Me.UsedRange.Sort Key1:=Range("A2"), Order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Application.EnableEvents = True
End If
End Sub

 

[David Schaleger]

Oh, that is SWEET! Thank you! Thank you!! Thank you!!!
Could you please add one more feature? I'd like the active cell after it sorts to be the cell in column B where the code sorts the new item to that I just added. So, if I added the new item at A50 (end of table), and it sorts this new item to A25, I'd like the active cell to be B25 so I can start entering the other relavent data without having to first find where the new item was sorted to.

 

[VoG]

If the entries in column B are unique then perhaps

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range, x As Variant
If Target.Column = 1 Then
    Application.EnableEvents = False
    x = Target.Offset(, 1).Value
    Me.UsedRange.Sort Key1:=Range("A2"), Order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Set Found = Columns("B").Find(what:=x, LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then Found.Select
    Application.EnableEvents = True
End If
End Sub