Sub Test()
Dim t, ncol%, tablo, nlig&, j%, f$, i&
t = [B2:J2] 'à adapter
ncol = UBound(t, 2)
tablo = [B4:B8].Resize(, ncol) 'à adapter
nlig = UBound(tablo)
For j = 1 To ncol
If t(1, j) <> "" Then
f = "=" & t(1, j)
For i = 1 To nlig
tablo(i, j) = f
Next
End If
Next
With [B4:B8].Resize(, ncol)
.FormulaLocal = tablo
.Value = .Value 'supprime les formules
End With
End Sub