Copy/Transpose into a matrix of merged cells. Use Offset formula orVBA ?

[u473]

The test source is an Excel worksheet of 7 rows and 5 Cols inclusive
of headers.
I cannot use Transpose because the destination is a matrix of merged
rows.
Cell A1 of the source will go in the merged cells of A1:A4 in the
destination sheet.
Cell B1 of the source will go in the merged cells of A5:A8 in the
destination sheet, etc...
How do I accomplish this ?. I have been trying to use Offset formulas
without satisfaction.
Would VBA be the answer ? If so how ?
Note : the merged rows are a constant merge of 4 rows.
The source sheet does not have merged cells.
Help appreciated.
J.P.

 

[Per Jessen]

This should do it:

Sub TransposeToMergedCells()
Dim StartCell As Range
Dim DestCell As Range

Set StartCell = Worksheets("Sheet1").Range("A1") 'Source sheet
Set DestCell = Worksheets("Sheet2").Range("A1") 'Destination sheet

For c = 1 To 5
For r = 1 To 7
DestCell.Offset(0, r - 1) = StartCell.Offset(r - 1, c - 1)
Next
Set DestCell = DestCell.Offset(1, 0)
Next
End Sub

 

[u473]

You made my day.
Thank you, very, very much.