Remove duplicates but keep last ones based on last activity

[Shepard]

Hello,

Allow me to explain my problem. I have the sheet with data on it (16 columns in total). Every day I have to add the new data to the the end of the list and remove duplicates based on three filters (row1: User ID, row2: some data and row4: Last Activity(consists of the dates)). When I process the data manually I don't experience any problem. To simplify my life I decided to create two VBA scripts (first to copy the data from external workbook, second to process the data).

for example I have the following data:

users from the previous day:
row1 row2 row4
User ID something last activity
77b 11.04.2022
77a 11.04.2022
77d 11.04.2022

today's users:
66a 12.04.2022
66b 12.04.2022
77d 12.04.2022

I need some solution to keep user 77d which appeared in 12.04.2022 list. When I use macro based on two filters it removes it. When I use three filters a lot of duplicates are not being removed. Could you please have a look at the macros beneath and find out what is wrong? Unfortunately I cannot share the workbook because it includes personal data of the users.

Macros1:

Sub Copy()

Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim i As Integer
Dim lastrow As Long
Dim verylastrow As Long


If MsgBox("Please make sure you upload Tableau report", vbOKCancel, "Just checking") = vbOK Then

Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(FileFilter:="Comma Separated Values Files (*.csv),*.csv")

If FileToOpen <> False Then

Set OpenBook = Application.Workbooks.Open(FileToOpen)
lastrow = ThisWorkbook.Worksheets("sheet1").Range("B9000").End(xlUp).Row + 1
OpenBook.Sheets(1).Range("A2", Range("O2").End(xlDown)).Copy ThisWorkbook.Worksheets("sheet1").Range("A" & lastrow)
OpenBook.Close savechanges:=False

End If

End If

verylastrow = ThisWorkbook.Worksheets("sheet1").Range("B9000").End(xlUp).Row
For i = lastrow To verylastrow
Cells(i, 16).Value = Date

Next

Application.ScreenUpdating = True
'Process_Data2()
End Sub



Macros2:

Sub Process_Data2()
'
' Process_Data2 Macro
'

'
Range("A1:P2860").Select
Range("E1986").Activate
ActiveSheet.Range("$A$1:$P$1048576").RemoveDuplicates Columns:=Array(1, 2, 4), _
Header:=xlYes
End Sub