automatic sort of a table, return to last used cell

[Wolfgang7]

Hi,
I have table where new entries hav to made
With the intersect mehodI manage to register the chngd lines after a cell has changed.
Now there is the requirement to sort the tabe after each new entry
and to stay after sorting in the active cell for further entries inthe same line.
I've tried it with temporary comment (see below), but i's hard :-(



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C3:W5000")) Is Nothing Then
'Range muss evtl. erweitert werden...
If Target.Column <> 24 Then

'Aenderungsinfo:
'Cells(Target.Row, 24)-> 24 steht für Spalte W, "Datum/Zeit/User,
'muss bei Spalttenverschiebung angepasst werden.
Cells(Target.Row, 24) = Now & " " & Environ("Username")
End If
'aktive Zelle mit Kommentar "marke" versehen
'Range (Application.PreviousSelections(1).Address)
'Range(ActiveCell.Address).Select
Range(ActiveCell.Address).AddComment
Range(ActiveCell.Address).Comment.Text Text:="marke"

'Sortierung:
'Bereich, auf den sich die Sortierung auswirken soll
Range("A4:W5000").Select
'Sortierkriterien
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

End If
End Sub

[p45cal]

try something along these lines:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C3:W5000")) Is Nothing Then
  'Range muss evtl. erweitert werden...
  If Target.Column <> 24 Then

    'Aenderungsinfo:
    'Cells(Target.Row, 24)-> 24 steht für Spalte W, "Datum/Zeit/User,
    'muss bei Spalttenverschiebung angepasst werden.
    myStr = Now & " " & Environ("Username")
    Application.EnableEvents = False
    Cells(Target.Row, 24) = myStr
    Application.EnableEvents = True
  End If
  'Sortierung:
  'Bereich, auf den sich die Sortierung auswirken soll
  Range("A4:X5000").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  'Note in the above line you were not including column X (column 24) where you've just put the name and timestamp!
  myRow = Columns(24).Find(myStr, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False).Row
  Cells(myRow, Target.Column).Select
End If
End Sub

[Wolfgang7]

Hi p45cal,
exactl what I need, your my hero for toay, elegant solution, life could be so easy, god help me with more brain