Private Sub Workbook_Activate()
'Feuil3 Feuil4 => CodeName des feuilles de destination
MAJ1 "année_2012.xlsx", Feuil3
MAJ2 "Chrono_C_2012.xlsx", Feuil4
End Sub
Sub MAJ1(fich$, F As Worksheet)
Dim Wb As Workbook, w As Worksheet, lig&, t$
On Error Resume Next 'si le fichier n'est pas ouvert
Set Wb = Workbooks(fich)
If Err Then Exit Sub
On Error GoTo 0
lig = 6
For Each w In Wb.Worksheets
t = "='[" & fich & "]" & w.Name & "'!"
F.Cells(lig, 1).Formula = t & "D1"
F.Cells(lig, 3).Formula = t & "E4"
F.Cells(lig, 4).Formula = t & "E7"
F.Cells(lig, 5).Formula = t & "J28"
F.Cells(lig, 6).Formula = t & "J12"
F.Cells(lig, 7).Formula = t & "A32"
F.Cells(lig, 9).Formula = t & "A43"
F.Cells(lig, 10).Formula = t & "D43"
F.Cells(lig, 11).Formula = t & "E43"
F.Cells(lig, 12).Formula = t & "H43"
lig = lig + 1
Next
F.Rows(lig & ":" & Rows.Count).ClearContents
End Sub
Sub MAJ2(fich$, F As Worksheet)
Dim Wb As Workbook, w As Worksheet, lig&, t$
On Error Resume Next 'si le fichier n'est pas ouvert
Set Wb = Workbooks(fich)
If Err Then Exit Sub
On Error GoTo 0
lig = 6
For Each w In Wb.Worksheets
t = "='[" & fich & "]" & w.Name & "'!"
'suite du code à adapter comme MAJ1
lig = lig + 1
Next
F.Rows(lig & ":" & Rows.Count).ClearContents
End Sub