Auto Sorting

[MarieB]

I would like to be able to enter data into a worksheet and have the data auto sort as it is entered. I have tried using macros (not real familiar with) and could not get it to successfully update. Here is an example of my worksheet.

<table border="0" cellpadding="0" cellspacing="0" width="652"><col width="103"><col width="180"><col width="70"><col width="87"><col width="212"><tr height="20"> <td class="xl72" style="height: 15pt; width: 77pt;" height="20" width="103">Ticket #</td> <td class="xl70" style="width: 135pt;" width="180">Agent
</td> <td class="xl65" style="width: 53pt;" width="70">Reviewer</td> <td class="xl64" style="width: 65pt;" width="87">Date</td> <td class="xl66" style="width: 159pt;" width="212">Comment
</td> </tr></table>

I would like column A (Ticket #) to be the primary sort and up date as data is entered.

Thank you for your help

 

[VoG]

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

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

 

[MarieB]

I used the developer tab and added this to the view code. Is there something I need to do activate the code once I add it? I entered a new line of data and it did not sort. I apologize, this is pretty foreign territory.

 

[VoG]

You shouldn't need the Developer tab:

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

 

[MarieB]

Ok, developer tab gone-added to sheet tab, still no luck.

 

[VoG]

Check on the Developer tab that macros are enabled (the lowest security setting).

 

[MarieB]

Enabled all macros - no go on sorting.

 

[VoG]

I've uploaded my test workbook here http://www.box.net/shared/ah4j68ltc2y705q5b2mt

 

[MarieB]

Should it auto sort after entering to the next row or do I need to do an additional step for this to happen. Thank you so much for your patience.

 

[VoG]

It will auto sort when anything on the sheet is changed. That might be an issue and maybe you want it to sort when one particular column value is changed.


Also it will crash if you paste to multiple cells. That can be handled like this

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