Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name Like "Véhic. Dipo.*?" Then Workbook_SheetChange Sh, Sh.[A1] 'lance la macro
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Sh.Name Like "Véhic. Dipo.*?" Then Exit Sub
Dim jour$, F As Worksheet, FF As Worksheet, dest As Range, d As Object, col, colF%, colFF%, tablo, i&, x$, resu$(), n&
jour = Trim(Mid(Sh.Name, InStrRev(Sh.Name, ".") + 1)) 'Lundi Mardi etc...
Set F = Sheets(jour) 'véhicules utilisés
Set FF = Sheets("Base des données") 'immatriculations
Set dest = Sh.[A1] '1ère cellule du tableau des résultats
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
If Sh.FilterMode Then Sh.ShowAllData 'si la feuille est filtrée
Set d = CreateObject("Scripting.Dictionary")
For Each col In Array(1, 3) 'colonnes A et C
colF = IIf(col = 1, 14, 15) 'colonnes N et O
colFF = IIf(col = 1, 5, 6) 'colonnes E et F
tablo = F.Cells(1, colF).Resize(F.Cells(F.Rows.Count, colF).End(xlUp).Row, 2) 'matrice, plus rapide, au moins 2 éléments
d.RemoveAll 'RAZ
For i = 2 To UBound(tablo)
x = tablo(i, 1)
If x <> "" Then d(x) = "" 'liste sans doublon
Next i
tablo = FF.Cells(1, colFF).Resize(FF.Cells(FF.Rows.Count, colFF).End(xlUp).Row, 2) 'matrice, plus rapide, au moins 2 éléments
ReDim resu(1 To UBound(tablo), 1 To 1)
n = 0 'RAZ
For i = 2 To UBound(tablo)
x = tablo(i, 1)
If x <> "" And Not d.exists(x) Then n = n + 1: resu(n, 1) = x
Next i
'---restitution---
If n Then dest(2, col).Resize(n) = resu
dest(1, col).Resize(n + 1).Borders.Weight = xlThin 'bordures
dest(2, col).Offset(n).Resize(Rows.Count - n - dest.Row).Delete xlUp 'RAZ en dessous
Next col
Application.EnableEvents = True 'réactive les évènements
End Sub