Gavin, in truth I think we would want more info in terms of where you want to paste the resulting values...
There are countless approaches to this type of thing... one (inelegant) approach being:
Public Sub CopyData()
Dim ws As Worksheet, bi As Byte, vData(1 To 4)
Set ws = Sheets("Sheet1")
For bi = 1 To 4
vData(bi) = Application.Choose(bi, ws.Range("A1"), ws.Range("B4"), ws.Range("C4"), ws.Range("H8"))
Next bi
Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(, 4).Value = vData
Set ws = Nothing
End Sub
Bookmarks