How To Copy / Paste Multiple Data "Blocks" Using Paste Special / Transpose - Need Advice

Dear @Infoman,

Good day, and thank you for reaching out to the Q&A Forum.

I appreciate the clear explanation of your issue. I understand that you have repeating blocks of 12 rows in Column A, and you would like to transpose each block into one row with 12 columns. From my research, there are several possible ways to achieve this in Excel. Below are two approaches that may help, depending on your Excel version and whether you prefer using a formula or VBA.

Please try the following options:

1.Use a modern Excel formula

If you are using Microsoft 365 Excel and have access to the WRAPROWS function, you can use this formula In cell B1(or wherever you want the output):

=WRAPROWS(FILTER(A1:A10000,A1:A10000<>""),12,"")

This formula takes all non-blank values from Column A and wraps them into rows of 12 columns.

Notes:

• Enter the formula only in B1.
• Do not copy it down.
• Make sure the output area, such as B1:M1000, is empty before entering the formula; otherwise, Excel may return a #SPILL! error.

If WRAPROWS is not available in your Excel version, you can try this alternative formula instead:

=LET(data,FILTER(A1:A10000,A1:A10000<>""),n,12,numBlocks,QUOTIENT(ROWS(data),n),INDEX(data,SEQUENCE(numBlocks,,0)*n+SEQUENCE(,n)))

2.Use VBA

1. Press Alt + F11 to open the VBA Editor.
2. Go to Insert > Module.
3. Paste the code below into the new module.

Sub TransposeBlocksOf12()
 Dim ws As Worksheet
 Dim LastRow As Long
 Dim BlockSize As Long
 Dim Data As Variant
 Dim Result() As Variant
 Dim r As Long
 Dim NonBlankCount As Long
 Dim OutputRows As Long
 Dim OutputRow As Long
 Dim OutputCol As Long
 Dim LastOutputRow As Long
 Dim c As Long
 Dim CellText As String
 Set ws = ActiveSheet
 BlockSize = 12
 ' Find last row in Column A
 LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 ' Read Column A into memory
 Data = ws.Range("A1:A" & LastRow).Value
 ' Count non-blank cells
 For r = 1 To UBound(Data, 1)
 If Not IsError(Data(r, 1)) Then
 If Trim(CStr(Data(r, 1))) <> "" Then
 NonBlankCount = NonBlankCount + 1
 End If
 End If
 Next r
 If NonBlankCount = 0 Then
 MsgBox "No data found in Column A.", vbExclamation
 Exit Sub
 End If
 ' Calculate how many output rows are needed
 OutputRows = Application.WorksheetFunction.RoundUp(NonBlankCount / BlockSize, 0)
 ' Prepare output array
 ReDim Result(1 To OutputRows, 1 To BlockSize)
 OutputRow = 1
 OutputCol = 1
 ' Fill output array
 For r = 1 To UBound(Data, 1)
 If Not IsError(Data(r, 1)) Then
 CellText = Trim(CStr(Data(r, 1)))
 If CellText <> "" Then
 Result(OutputRow, OutputCol) = CellText
 OutputCol = OutputCol + 1
 If OutputCol > BlockSize Then
 OutputCol = 1
 OutputRow = OutputRow + 1
 End If
 End If
 End If
 Next r
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 ' Clear previous output from B:M only
 For c = 2 To 13
 LastOutputRow = Application.Max(LastOutputRow, ws.Cells(ws.Rows.Count, c).End(xlUp).Row)
 Next c
 ws.Range(ws.Cells(1, 2), ws.Cells(Application.Max(LastOutputRow, OutputRows), 13)).ClearContents
 ' Write result starting from B1
 ws.Range("B1").Resize(OutputRows, BlockSize).Value = Result
 Application.EnableEvents = True
 Application.ScreenUpdating = True
 MsgBox "Transpose completed successfully!" & vbCrLf & _
 OutputRows & " rows created starting from B1.", vbInformation
End Sub

After pasting the code:

1. Close the VBA Editor.
2. Press Alt + F8.
3. Select TransposeBlocksOf12, then click Run.

Notes:

• The macro reads the values from Column A.
• The output starts from B1.
• It groups every 12 non-blank values into one row.
• It clears the previous output in B:M before writing the new result, so old results do not remain.
• Please run the macro on a copy of your workbook first, just to be safe.

I hope this information is helpful. Please try the steps above and let me know how it goes. If you run into any issue, feel free to share more details, and I’ll be happy to continue assisting. Thank you for your patience and understanding. I look forward to your reply.Have a wonderful day!

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.