Option Explicit
Sub Macro1()
Dim Cell As Range
Dim Classeur As Workbook
Dim Chemin As String
Dim Fichier As String
Dim WB As Worksheet
Set WB = Worksheets(1)
Chemin = ThisWorkbook.Path & "/"
Fichier = "Fichier-source.xlsx"
MsgBox Chemin
Set Classeur = GetObject(Chemin & Fichier) 'Le fichier est ouvert mais masqué
With Classeur
For Each Cell In WB.Range("A:A").SpecialCells(xlCellTypeConstants, 23)
Cell.Offset(, 5).ClearContents 'Colonne F
Cell.Offset(, 5).FormulaR1C1 = "=INDEX(INDIRECT(""'[""&R1C10&""]""&R2C10&""'!$A:$E""),MATCH(R1C5,INDIRECT(""'[""&R1C10&""]""&R2C10&""'!$A:$A""),0),MATCH(RC[-5],INDIRECT(""'[""&R1C10&""]""&R2C10&""'!$1:$1""),0))"
Cell.Offset(, 5).Value = Cell.Offset(, 5).Value
Next Cell
.Save
DoEvents
.Close
End With
End Sub