VBA auto sort a dynamic list with formulas

[Schrankwand2006]

Hello all,

I have a problem and desperately need your help!

I have a workbook with 2 sheets. Sheet1 contains a large list with 5 columns (B:F) with the header on row 2 and the data on rows 2 to 225.

The columns are:
B: Date
C: Department
D: Employee
E: Cost

With an array formula I managed to create another list next to it allowing me to dynamically filter the main list, for example showing only Department 6. Pretty much like automatic filter by with a formula so it happens dynamically. Please see the attached workbook with the example.

On a second sheet I want to show the dynamic list (i.e. Department 6) but automatically sorted by the date.

For this sheet I included the following VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2:A70")) Is Nothing Then Exit Sub
Application.EnableEvents = False

Me.Sort.SortFields.Clear
Me.Sort.SortFields.Add Key:=Range("A2:A70"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Me.Sort
    .SetRange Range("A2:E70")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Application.EnableEvents = True
End Sub

If the list on sheet1 changes, it should automatically update the filtered and sorted list on sheet2. But that doesn’t yet work. I have absolutely no idea about VBAs and only found the above in this forum. It works if you click into one of the cells on sheet2 and click enter. But it doesn’t happen automatically, unfortunately.

How do I need to change the VBA text to make this happen?

I hope I was able to explain, it is all very new to me, so please be kind with your replies!!

Thank you for your help and in case of any questions, pls do let me know!