Private Sub Worksheet_Activate()
If MsgBox("Confirmez vous la M.A.J du tableau ?", vbYesNo, "Confirmation") = vbNo Then Exit Sub
[A2:I1000].ClearContents
Range("A1").Value = "RECAPITULATIF des Visites EF 5A n°7"
Range("A2").Value = "N° du CR"
Range("B2").Value = "Dates"
Range("C2").Value = "Lieux"
Range("D2").Value = "N° Points"
Range("E2").Value = "Installations"
Range("F2").Value = "Points à Amortir"
Range("G2").Value = "Delais"
Range("H2").Value = "Moyen"
DL = 3 'DL = Derniere Ligne
For I = 5 To Sheets.Count 'Tous les onglets a partir du 5eme
nf = Sheets(I).Name
With Sheets(I)
NL = .Range("L1").Value 'Nb de ligne sur l'onglet
For L = 12 To NL + 11
If .Range("G" & L).Value <> "" And .Range("H" & L).Value = "" Then
ActiveSheet.Hyperlinks.Add Anchor:=Cells(DL, 1), Address:="", SubAddress:="'" & _
nf & "'" & "!A1", TextToDisplay:=nf
ActiveSheet.Range("B" & DL).Value = .Range("C8").Value
ActiveSheet.Range("C" & DL).Value = .Range("B12").Value
ActiveSheet.Range("D" & DL).Value = .Range("A" & L).Value
ActiveSheet.Range("E" & DL).Value = .Range("C" & L).Value
ActiveSheet.Range("F" & DL).Value = .Range("D" & L).Value
ActiveSheet.Range("G" & DL).Value = .Range("G" & L).Value
ActiveSheet.Range("H" & DL).Value = .Range("E" & L).Value
DL = DL + 1
End If
Next L
End With
Next I
Range("A2:H2").Select
Selection.AutoFilter
Columns("A:H").Select
ActiveWindow.Zoom = True
Range("I1").Value = DL
Range("I1").Select
End Sub