auto filter on adding rows

[austenr]

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.

[Paul_Hossler]

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



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, ...?

[austenr]

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.

[Paul_Hossler]

I'd do something like this


In standard module



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



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

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

[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?

[Paul_Hossler]

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


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

20991

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

[austenr]

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???

[Paul_Hossler]

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

See if that works better




'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

[austenr]

thanks Paul. Works great.