It occurs to me to add that I want to use the formula in this macro.

.Formula = "New and exciting dynamic Vlookup formula)"
.Value = .Value


Option Explicit

Sub Sh1_To_Sh2()
With Sheets("Sheet2").Range("D4:D" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = "=VLOOKUP(B4,Sheet1!E1:F6,2,0)"
.Value = .Value
End With
End Sub

Howard