Sub Alerte()
Dim Ligne As Long, C As Range, Plage As Range, Tabl As Variant, I As Long, Teste As Boolean
Dim Tot As Long
With Sheets("Agr")
Set Plage = .Range("J2", .Cells(.Rows.Count, 10).End(xlUp))
End With
With Sheets("Utilisateurs")
Ligne = .Cells(.Rows.Count, 8).End(xlUp).Row
Tabl = Application.Transpose(.Range("H2:H" & Ligne))
For Each C In Plage
For I = 1 To UBound(Tabl)
Teste = False
If C = Tabl(I) Then
Teste = True
Exit For
End If
Next I
If Teste = False Then
Ligne = Ligne + 1
.Cells(Ligne, 8) = C.Value
Tabl = Application.Transpose(.Range("H2:H" & Ligne))
Tot = Tot + 1
End If
Next C
If Tot > 0 Then
MsgBox Tot & " nouveaux utilisateur(s)"
Else
MsgBox "Pas de nouveau utilisateur"
End If
End With
'-----------page Hat
With Sheets("Hat")
Set Plage = .Range("J2", .Cells(.Rows.Count, 10).End(xlUp))
End With
With Sheets("Utilisateurs")
Ligne = .Cells(.Rows.Count, 8).End(xlUp).Row
Tabl = Application.Transpose(.Range("H2:H" & Ligne))
For Each C In Plage
For I = 1 To UBound(Tabl)
Teste = False
If C = Tabl(I) Then
Teste = True
Exit For
End If
Next I
If Teste = False Then
Ligne = Ligne + 1
.Cells(Ligne, 8) = C.Value
Tabl = Application.Transpose(.Range("H2:H" & Ligne))
Tot = Tot + 1
End If
Next C
If Tot > 0 Then
MsgBox Tot & " nouveaux utilisateur(s)HAt"
Else
MsgBox "Pas de nouveau utilisateur HAT"
End If
End With
'----------------- page CA
With Sheets("CA")
Set Plage = .Range("J2", .Cells(.Rows.Count, 10).End(xlUp))
End With
With Sheets("Utilisateurs")
Ligne = .Cells(.Rows.Count, 8).End(xlUp).Row
Tabl = Application.Transpose(.Range("H2:H" & Ligne))
For Each C In Plage
For I = 1 To UBound(Tabl)
Teste = False
If C = Tabl(I) Then
Teste = True
Exit For
End If
Next I
If Teste = False Then
Ligne = Ligne + 1
.Cells(Ligne, 8) = C.Value
Tabl = Application.Transpose(.Range("H2:H" & Ligne))
Tot = Tot + 1
End If
Next C
If Tot > 0 Then
MsgBox Tot & " nouveaux utilisateur(s)CA"
Else
MsgBox "Pas de nouveau utilisateur CA"
End If
End With
'--------------page Att
With Sheets("Att")
Set Plage = .Range("J2", .Cells(.Rows.Count, 10).End(xlUp))
End With
With Sheets("Utilisateurs")
Ligne = .Cells(.Rows.Count, 8).End(xlUp).Row
Tabl = Application.Transpose(.Range("H2:H" & Ligne))
For Each C In Plage
For I = 1 To UBound(Tabl)
Teste = False
If C = Tabl(I) Then
Teste = True
Exit For
End If
Next I
If Teste = False Then
Ligne = Ligne + 1
.Cells(Ligne, 8) = C.Value
Tabl = Application.Transpose(.Range("H2:H" & Ligne))
Tot = Tot + 1
End If
Next C
If Tot > 0 Then
MsgBox Tot & " nouveaux utilisateur(s)Att "
Else
MsgBox "Pas de nouveau utilisateur Att"
End If
End With
'-----------page perso
With Sheets("perso")
Set Plage = .Range("J2", .Cells(.Rows.Count, 10).End(xlUp))
End With
With Sheets("Utilisateurs")
Ligne = .Cells(.Rows.Count, 8).End(xlUp).Row
Tabl = Application.Transpose(.Range("H2:H" & Ligne))
For Each C In Plage
For I = 1 To UBound(Tabl)
Teste = False
If C = Tabl(I) Then
Teste = True
Exit For
End If
Next I
If Teste = False Then
Ligne = Ligne + 1
.Cells(Ligne, 8) = C.Value
Tabl = Application.Transpose(.Range("H2:H" & Ligne))
Tot = Tot + 1
End If
Next C
If Tot > 0 Then
MsgBox Tot & " nouveaux utilisateur(s) perso"
Else
MsgBox "Pas de nouveau utilisateur perso"
End If
End With
End Sub