auto filter on adding rows

title should be dynamic sort on adding rows. Would like some sort of worksheet change event code.

I think I have found this on here before but its been years.

I will be adding rows to a workbook each week. I need them to automatically sort on columns A,C and D. Columns C and D have dates in them so I need to keep them in date order.

total columns are A-E. It might have been Zack who helped me with this when he was still on frequently around 2004. I cant seem to locate anything close to what I want.

Quote:

---

Would like some sort of worksheet change event code.

---

I'd make it part of the Worksheet_Deactivate event since you might be adding a number of rows with multiple columns

I don't think I'd like it activating each time there was ANY change

Quote:

---

total columns are A-E.

---

You mean A and E? Not A through E?

How are the totals calculated: one day, running, beginning to end, ...?

Should have posted the WB. On the attached Sheet1 I need to add a weeks worth of rows like my name shows. The calculation on each row from start time to end time difference doesn't seem correct. Im not going to be running this so I need a way for the user to just paste the rows at the end of the spreadsheet and sort them in the correct order.

I'd do something like this


In standard module

Code:

---

Option Explicit

Sub SortData(ws As Worksheet)

    Dim r As Range, r1 As Range
   
    Application.ScreenUpdating = False
       
    With ws
       
        Set r = .Range("A6").CurrentRegion
        Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
   
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=r1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=r1.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=r1.Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=r1.Columns(4), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange r
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
   
        Set r = .Range("A6").CurrentRegion
    End With
       
    Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
       
    With r1
        .HorizontalAlignment = xlCenter
        .Columns(3).NumberFormat = "m/d/yyyy h:mm"
        .Columns(4).NumberFormat = "m/d/yyyy h:mm"
        .Columns(5).Formula = "=IF(OR(C7=0,D7=0),"""",D7-C7)"
        .Columns(5).NumberFormat = "h:mm"
    End With
    Application.ScreenUpdating = False

'    MsgBox "Sorted"

End Sub

---

In Sheet1 code module

Code:

---

Private Sub Worksheet_Activate()
    Call SortData(Me)
End Sub

Private Sub Worksheet_Deactivate()
    Call SortData(Me)
End Sub

---

Hi Paul

Thanks for the help. when I paste the new rows at the bottom it doesnt sort it into alphabetical order. Am I missing something? Also is there a way to add two combo boxes to filter for a specific date range?

Quote:

---

Originally Posted by austenr

Hi Paul

Thanks for the help. when I paste the new rows at the bottom it doesnt sort it into alphabetical order. Am I missing something? Also is there a way to add two combo boxes to filter for a specific date range?

---

1. Did you exit and return to the data sheet? The way I have the sort is that it only runs when entering or exiting the worksheet, and only if EnableEvents = True

From Post #4

Code:

---

Private Sub Worksheet_Activate()
    Call SortData(Me)
End Sub
 
Private Sub Worksheet_Deactivate()
    Call SortData(Me)
End Sub

---

2. I added Bill in row 22, went to Sheet2, and then back

Attachment 20991

3. The event can be changed to Worksheet_Change, but that seemed like too many firings

I see. I got that to work. something i do not understand is why the paste function is disabled on sheet1? The user will want to just past the new data into the sheet switch sheets and come back having it sorted. I tried to paste rows from sheet 2 to sheet 1 but the paste function was disabled???

In the Sheet1 code module delete these two subs, and add the Worksheet_Change one

See if that works better

Code:

---

'Private Sub Worksheet_Activate()
'    Call SortData(Me)
'End Sub
'Private Sub Worksheet_Deactivate()
'    Call SortData(Me)
'End Sub

 
Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Intersect(Target, Me.Range("B6").CurrentRegion) Is Nothing Then Exit Sub
   
    Application.EnableEvents = False
    Call SortData(Me)
    Application.EnableEvents = True
End Sub

---

thanks Paul. Works great.