Sub Synthèse()
Windows("Classeur avec les feuilles.xlsx").Activate
Sheets.Add(After:=ActiveSheet).Name = "SYNTHESE"
With Sheets("SYNTHESE")
.[A4:A7] = Application.Transpose(Array("Name 1", "Name 2", "Name 3", "Name 4"))
.[B3:C3] = Array("Item 1", "Item 2")
.Range("B4").FormulaR1C1 = "='Name 1'!R[2]C"
.Range("C4").FormulaR1C1 = "='Name 1'!R[4]C[-1]"
.Range("B5").FormulaR1C1 = "='Name 2'!R[1]C"
.Range("C5").FormulaR1C1 = "='Name 2'!R[3]C[-1]"
.Range("B6").FormulaR1C1 = "='Name 3'!RC"
.Range("C6").FormulaR1C1 = "='Name 3'!R[2]C[-1]"
.Range("B7").FormulaR1C1 = "='Name 4'!R[-1]C"
.Range("C7").FormulaR1C1 = "='Name 4'!R[1]C[-1]"
End With
End Sub