Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
Dim i As Integer
Sheets("Piquets2").Activate
'recherche des dates 6 mois antérieurs
For i = 2 To 150
Cells(i, 1) = Date - (140 - i)
Next i
Call MAJPiquets
End Sub
Private Sub MAJPiquets()
Dim gardedujour As String
Dim j As Integer
For j = 2 To 150
jourgarde = Cells(j, 1)
jourgarde = Format(jourgarde, "dd/mm/yyyy")
gardedujour = ThisWorkbook.Path & "\" & Format(jourgarde, "yyyy") & "\" & Format(jourgarde, "mmmmyyyy") & "\"
'chef de garde
Cells(j, 2) = "='" & gardedujour & "\[" & Format(jourgarde, "ddmmmmyyyy"".xls") & "]01'!$AC$4"
'ronde
Cells(j, 3) = "='" & gardedujour & "\[" & Format(jourgarde, "ddmmmmyyyy"".xls") & "]01'!$AX$7"
'stationnaire jour
Cells(j, 4) = "='" & gardedujour & "\[" & Format(jourgarde, "ddmmmmyyyy"".xls") & "]01'!$AR$4"
'stationnaire nuit
Cells(j, 5) = "='" & gardedujour & "\[" & Format(jourgarde, "ddmmmmyyyy"".xls") & "]01'!$AZ$4"
'sous off de jour
Cells(j, 6) = "='" & gardedujour & "\[" & Format(jourgarde, "ddmmmmyyyy"".xls") & "]01'!$AM$7"
Next j
Application.Calculation = xlCalculationAutomatic
End Sub