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



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
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.

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)
Set DestCell = DestCell.Offset(1, 0)
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question