Macro that automatically sorts by date without pressing a shortcut

[bellfellas]

Hi everyone - I'm sure this has been asked before, but I had a hard time finding it in the forum.

So I'm working on a table with a date column, issue column, effects column, comments column, and training provided column.

My co-worker wants to be able to add a new record including a date, and have that entire row automatically jump into an organized by date, chronological position.

Sometimes the new entries will be current dates, other times they might be older dates (in which case they might have to jump up in the list quite a bit).

The farthest I've gotten with the VBA macro is creating one with a shortcut key and relative pathways, but neither of those things work because my co-worker wants it to happen automatically when the record is entered, and relative pathways don't work because the sort doesn't end up applying to the whole table. (It's just all I could get working.)

Does anyone have any ideas? Here's the Macro I had:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+u
'

ActiveCell.Offset(-11, 0).Range("A1:A500").Select
ActiveWindow.SmallScroll Down:=-30
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=ActiveCell, _

SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers

With ActiveWorkbook.Worksheets("Sheet1").Sort

.SetRange ActiveCell.Range("A1:E12")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With
ActiveCell.Offset(1, 0).Range("A1").Select

End Sub

Here's basically what the table looks like and I've attached it as well:

ExcelCapture.JPG

Test.xlsm

[codeslizer]

Hi Fella,

For this purpose you should use worksheet's change event as it gets invoked whenever the changes are made to the cell values. Following is the implementation.

Code to Sort : Based on your example:

Please Login or Register  to view this content.

and code for change event

Please Login or Register  to view this content.

[bellfellas]

Awesome! Thank you so so much!! This is working perfectly!

[codeslizer]

Glad to help!