Auto sort after date stamp

[HDavidson]

I am using the Vba code below to apply a date stamp in a spreadsheet .How can i adapt this to auto sort all the data with newest entry at the top.The spreadsheet covers the columns A to J inclusive

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row > 1 Then Cells(Target.Row, "j") = Now()
End Sub

Thanks

 

[HDavidson]

well i thought it was ??.When i enter any data the last entry does not go to the top of the list ie row 2 it only moves up to the row below the previous entry.If you follow this through and keep adding data it means the latest entry would be the last and not the first as i needed.Hope you can assist.


Thanks

 

[James006]

Re,

Is the following macro ... in line with your expectations ...?

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Count > 1 Then Exit Sub
   Dim last As Long
   Application.ScreenUpdating = False
   Application.EnableEvents = False
   If Target.Row > 1 Then Cells(Target.Row, "J") = Now()
   last = ActiveSheet.Cells(Application.Rows.Count, "J").End(xlUp).Row
   Range("A1:J" & last).Sort [J1], xlDescending, Header:=xlYes
   Application.EnableEvents = True
   Application.ScreenUpdating = True
End Sub

HTH

 

[HDavidson]

Super job James your a star.Thanks again for all you help how easy would it be to add columns if i extended the spreadsheet.

 

[James006]

Glad it is now all working as expected ...

Should you extend your spreadsheet :

the only thing to do would to adjust the Column Letter J in Red ...to whatever new final column you might have

Range("A1:J" & last).Sort [J1], xlDescending, Header:=xlYes

All the rest has to remain as is ...

Hope this clarifies ...

 

[HDavidson]

what if i add a column before column a that i dont want to be included in the sort would i just change a1 to b1.


Thanks

 

[James006]

what if i add a column before column a that i dont want to be included in the sort would i just change a1 to b1.

Thanks

Yes ... you are right ...!!!