Sub test()
Dim i, j, k, l, M, O, P As Integer
Dim Somme, Total As Long
Dim cel As Range
Dim Feuil As Worksheet
Application.ScreenUpdating = False
If Worksheets.Count > 1 Then
For Each Feuil In ThisWorkbook.Worksheets
If Feuil.Name <> "Saisie CA" Then
Application.DisplayAlerts = False: Feuil.Delete: Application.DisplayAlerts = True
End If
Next
End If
For Each cel In Sheets("Saisie CA").Range("B3:B" & Sheets("Saisie CA").[b65000].End(xlUp).Row)
For i = 3 To Sheets("Saisie CA").[b65000].End(xlUp).Row
If WorksheetFunction.CountIf(Sheets("Saisie CA").Range("B3:B" & i + j), cel) = 1 Then
With Worksheets.Add
.Name = cel: .Cells(1, 1) = "Nom du Représentant": .Cells(1, 4) = cel
.Cells(2, 1) = "Date": .Cells(2, 2) = "Secteur": .Cells(2, 3) = "Magasin": .Cells(2, 4) = "CA Réalisé"
For k = .Cells(cel.Row, 2).Row To Sheets("Saisie CA").[b65000].End(xlUp).Row
If Sheets("Saisie CA").Range("B" & k) = cel And _
WorksheetFunction.CountIf(.Range("B3:B" & 3 + l), Sheets("Saisie CA").Cells(cel.Row + M, 1)) < 1 Then
.Cells(3 + l, 1) = CDate(Sheets("Saisie CA").Range("B1"))
.Cells(3 + l, 2) = Sheets("Saisie CA").Cells(cel.Row + M, 1)
.Cells(3 + l, 3) = Sheets("Saisie CA").Cells(cel.Row + M, 3)
For O = .Cells(cel.Row, 2).Row To Sheets("Saisie CA").[b65000].End(xlUp).Row
If Sheets("Saisie CA").Cells(cel.Row + P, 1) = Sheets("Saisie CA").Cells(cel.Row + M, 1) And Sheets("Saisie CA").Cells(cel.Row + P, 2) = cel Then
Somme = Sheets("Saisie CA").Cells(cel.Row + P, 4)
Total = Total + Somme
End If
P = P + 1
Next
.Cells(3 + l, 4) = Total
l = l + 1
P = 0
Somme = 0
Total = 0
End If
M = M + 1
Next
Exit For
End With
End If
Exit For
Next
With ActiveSheet
.Range("B3:D" & [D65000].End(xlUp).Row).Sort key1:=.Range("B3:B" & [D65000].End(xlUp).Row)
.Range("A2:D" & [D65000].End(xlUp).Row).Borders.LineStyle = 1
.Range("A2:D2").Font.Color = RGB(0, 0, 200): .Range("A2:D2").Font.Bold = True
End With
j = j + 1
k = 0: l = 0: M = 0
Next
Sheets("Saisie CA").Select
Application.ScreenUpdating = True
End Sub