chemin variable pour lire plusieur classeur

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Dim MaDate As Date, I As Integer, J As Integer
Const NbJours As Integer = 7
MaDate = CDate(Mid(Target, 6, 10))
For J = 1 To 3
For I = -1 To -NbJours Step -1
Range("Q" & J * 10 - 9).Formula = "='[Payroll " & Format(MaDate + I + 1, "yyyy-mm-dd") & ".xls]Daily (Payroll) (Total)'!$A$" & J + 3
Range("O" & J * 10 + 1 + I).Formula = "='[Payroll " & Format(MaDate + I + 1, "yyyy-mm-dd") & ".xls]Daily (Payroll) (Total)'!$C$" & J + 3
Range("P" & J * 10 + 1 + I).Formula = "='[Payroll " & Format(MaDate + I + 1, "yyyy-mm-dd") & ".xls]Daily (Payroll) (Total)'!$E$" & J + 3
Range("Q" & J * 10 + 1 + I).Formula = "='[Payroll " & Format(MaDate + I + 1, "yyyy-mm-dd") & ".xls]Daily (Payroll) (Total)'!$D$" & J + 3
Range("R" & J * 10 + 1 + I).Formula = "='[Payroll " & Format(MaDate + I + 1, "yyyy-mm-dd") & ".xls]Daily (Payroll) (Total)'!$F$" & J + 3
Range("S" & J * 10 + 1 + I).Formula = "='[Payroll " & Format(MaDate + I + 1, "yyyy-mm-dd") & ".xls]Daily (Payroll) (Total)'!$G$" & J + 3
Range("T" & J * 10 + 1 + I).Formula = "='[Payroll " & Format(MaDate + I + 1, "yyyy-mm-dd") & ".xls]Daily (Supervisor A)'!$P$" & J * 2 + 5
Range("U" & J * 10 + 1 + I).Formula = "='[Payroll " & Format(MaDate + I + 1, "yyyy-mm-dd") & ".xls]Daily (Supervisor B)'!$S$" & J * 2 + 6
Range("V" & J * 10 + 1 + I).Formula = "='[Payroll " & Format(MaDate + I + 1, "yyyy-mm-dd") & ".xls]Daily (Supervisor C)'!$P$" & J * 2 + 6
Range("W" & J * 10 + 1 + I).Formula = "='[Payroll " & Format(MaDate + I + 1, "yyyy-mm-dd") & ".xls]Daily (Supervisor C)'!$P$" & J * 2 + 5
Next I
Next J
Application.Calculation = xlCalculationAutomatic
For J = 1 To 3
Range("Q" & J * 10 - 9) = Range("Q" & J * 10 - 9).Value
Range("P" & J * 10 - 6 & ":W" & J * 10) = Range("P" & J * 10 - 6 & ":W" & J * 10).Value
Next J
Application.ScreenUpdating = True
End Sub