hello all

i have the following macro that "auto filters" based on the selection in column K (which are dates).

now the issue is, if i select a date, the macro upon "event change" will automatically filter the data in the respective table below row 70 on the sheet. but if i change the date in column K, it will not "clear the data" in row 70 etc so the original data is kept in row 70 and also the new date data - which is incorrect.

can someone pls help me with the logic here.



Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Address, 3) = "$K$" Then
Dim srt As String
srt = Right(Target.Address, Len(Target.Address) - Len(Left(Target.Address, 3)))

For i = 1 To 4
Select Case i
Case 1
 
For j = 70 To 109
If (ActiveSheet.Rows(j).Cells(1, 2) = ActiveSheet.Range("$L$" & srt) And _
ActiveSheet.Rows(CInt(srt)).Cells(1, 5) = ActiveSheet.Rows(j).Cells(1, 5)) Or _
(ActiveSheet.Rows(j).Cells(1, 2) = ActiveSheet.Range("$L$" & srt) And _
ActiveSheet.Rows(j).Cells(1, 5) = "") Then
 
 
Application.EnableEvents = False
ActiveSheet.Rows(j).Cells(1, 4) = "Insert"
Set Rng = ActiveSheet.Range("D" & srt & ":P" & srt)
Rng.Copy 'ActiveSheet.Range("D" & CStr(j) & ":P" & CStr(j))
ActiveSheet.Range("D" & CStr(j) & ":P" & CStr(j)).PasteSpecial xlPasteValues
Rng.Select

Application.EnableEvents = True
j = 109
 
End If
 
 
Next j

Case 2
'MsgBox (ActiveSheet.Range("$M$" & CStr(CInt(srt))))
For j = 70 To 109
If (ActiveSheet.Rows(j).Cells(1, 2) = ActiveSheet.Range("$M$" & srt) And _
ActiveSheet.Rows(CInt(srt)).Cells(1, 5) = ActiveSheet.Rows(j).Cells(1, 5)) Or _
(ActiveSheet.Rows(j).Cells(1, 2) = ActiveSheet.Range("$M$" & srt) And _
ActiveSheet.Rows(j).Cells(1, 5) = "") Then
 
Application.EnableEvents = False
ActiveSheet.Rows(j).Cells(1, 4) = "Insert"
Set Rng = ActiveSheet.Range("D" & srt & ":P" & srt)
Rng.Copy 'ActiveSheet.Range("D" & CStr(j) & ":P" & CStr(j))
ActiveSheet.Range("D" & CStr(j) & ":P" & CStr(j)).PasteSpecial xlPasteValues
Application.EnableEvents = True
'do insert operation
j = 109

End If
 
 
Next j
'MsgBox (ActiveSheet.Range("$M$" & CStr(CInt(srt))))
Case 3
'MsgBox (ActiveSheet.Range("$N$" & CStr(CInt(srt))))

For j = 70 To 109
If (ActiveSheet.Rows(j).Cells(1, 2) = ActiveSheet.Range("$M$" & srt) And _
ActiveSheet.Rows(CInt(srt)).Cells(1, 5) = ActiveSheet.Rows(j).Cells(1, 5)) Or _
(ActiveSheet.Rows(j).Cells(1, 2) = ActiveSheet.Range("$M$" & srt) And _
ActiveSheet.Rows(j).Cells(1, 5) = "") Then
 
Application.EnableEvents = False
ActiveSheet.Rows(j).Cells(1, 4) = "Insert"
Set Rng = ActiveSheet.Range("D" & srt & ":P" & srt)
Rng.Copy 'ActiveSheet.Range("D" & CStr(j) & ":P" & CStr(j))
ActiveSheet.Range("D" & CStr(j) & ":P" & CStr(j)).PasteSpecial xlPasteValues
Application.EnableEvents = True
'do insert operation
j = 109

End If
 
 
Next j
Case 4
'MsgBox (ActiveSheet.Range("$O$" & CStr(CInt(srt))))

For j = 70 To 109
If (ActiveSheet.Rows(j).Cells(1, 2) = ActiveSheet.Range("$M$" & srt) And _
ActiveSheet.Rows(CInt(srt)).Cells(1, 5) = ActiveSheet.Rows(j).Cells(1, 5)) Or _
(ActiveSheet.Rows(j).Cells(1, 2) = ActiveSheet.Range("$M$" & srt) And _
ActiveSheet.Rows(j).Cells(1, 5) = "") Then
 
Application.EnableEvents = False
ActiveSheet.Rows(j).Cells(1, 4) = "Insert"
Set Rng = ActiveSheet.Range("D" & srt & ":P" & srt)
Rng.Copy 'ActiveSheet.Range("D" & CStr(j) & ":P" & CStr(j))
ActiveSheet.Range("D" & CStr(j) & ":P" & CStr(j)).PasteSpecial xlPasteValues
Application.EnableEvents = True
'do insert operation
j = 109

End If
 
 
Next j
End Select
 
Next i
End If
Application.CutCopyMode = False
End Sub
thanking you in advance.