Private Sub Workbook_Open()
Dim n1, n2, F As Worksheet, w As Worksheet
n1 = 1 '1ère valeur recherchée, à adapter
n2 = 31 'dernière valeur recherchée, à adapter
Set F = Sheets("Synthèse")
Application.ScreenUpdating = False
F.AutoFilterMode = False 'au cas où...
F.range("A2:I" & F.Rows.Count).Delete xlUp 'RAZ
For Each w In Worksheets
If w.Name <> F.Name Then
With Intersect(w.[A:I], w.UsedRange.EntireRow)
.Columns(9) = "=""" & w.Name & "!A""&" & "ROW()"
.Columns(9) = .Columns(9).Value 'supprime les formules
.AutoFilter 1, ">=" & n1, xlAnd, "<=" & n2 'filtre automatique
.Offset(1).Copy F.range("A" & F.Rows.Count).End(xlUp)(2)
w.AutoFilterMode = False
.Columns(9) = ""
End With
End If
Next
F.Columns(9).HorizontalAlignment = xlGeneral
F.Columns(9).AutoFit
F.Activate 'facultatif
Application.ScreenUpdating = True
n1 = F.range("A" & F.Rows.Count).End(xlUp).Row - 1
MsgBox "Vous avez " & n1 & " contrôles obligatoires à effectuer ce mois-ci !"
End Sub