Transpose cells from multiple rows to a single row

Question

I have the below code to transpose the first cell of each row onto a single row, but I'm not sure how to amend it so that it takes the first 5 cells of each row and does the same?

ie I have this data in rows A-E -

and the code will copy the dates in column A as below -

But what I would like is the data transposed as below and so on. It also creates a new worksheet to transpose onto, what I would ideally like is for it to copy to an existing sheet (for arguments sake Sheet1) -

Option Explicit

Sub Transpose_List()

    Dim WS As Worksheet
    Dim WSA As Worksheet
    Dim NextRow As Long
    Dim LastRow As Long
    Dim i As Long

    Set WSA = ActiveSheet

    Set WS = ActiveWorkbook.Sheets.Add(Before:=Worksheets(Worksheets.Count))
    WS.Name = "Transposed"

    LastRow = WSA.Cells(Rows.Count, 1).End(xlUp).row

    Application.ScreenUpdating = False

    For i = 5 To LastRow Step 6
        NextRow = WS.Cells(Rows.Count, 1).End(xlUp).row + 1
        WSA.Cells(i, 1).Resize(6, 1).Copy
        WS.Cells(NextRow, 1).PasteSpecial Transpose:=True
    Next i

    WS.Columns("A:F").AutoFit

    Application.ScreenUpdating = True

End Sub

Take your data into array and then paste in the new sheet as you wish — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Sep 15, 2023 at 10:31
A formula would work: =TEXTSPLIT(TEXTJOIN(",",FALSE,A5:E10),",") — CLR
– CLR, Commented Sep 15, 2023 at 10:35
What happened when you changed your code to paste into an existing sheet instead of creating a new sheet? Or do you not understand the code you are using. I would think all you need to do is to Set WS to an existing workbook/worksheet and remove the code that is creating the new sheet. — Ron Rosenfeld
– Ron Rosenfeld, Commented Sep 15, 2023 at 11:09
You could use VBA (as you state you want to use that) to write that formula to a cell. I guess there's more to your requirement that you've not put here, as a single cell with a single formula is all you need. — CLR
– CLR, Commented Sep 15, 2023 at 12:28

Black cat · Accepted Answer · 2023-09-15 11:11:36Z

0

Target row is the row in the Sheet1 to transpose array. (Set to desired value)

ActiveSheet has to be the source sheet.

Sub transi()
targetrow = 3
For i = 5 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("A" & i & ":E" & i).Copy Worksheets("Sheet1").Cells(targetrow, 1 + (i - 5) * 5)
Next i
End Sub

If want to remove the range from the source sheet, change Copy to Cut.

answered Sep 15, 2023 at 11:11

Black cat

7,6358 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ffc2004 Over a year ago

Thank you! If I wanted to amend this to copy A-E, but then also include U-V, what would I need to change?

ffc2004 Over a year ago

I have tried the below, but it only adds on U-V for the final row, not the preceding ones also. ActiveSheet.Range("A" & i & ":E" & i & ",U" & i & ":V" & i).Copy Worksheets("Monthly CSV").Cells(targetrow, 1 + (i - 5) * 5)

ffc2004 Over a year ago

Never mind, I got there in the end! ActiveSheet.Range("A" & i & ":E" & i & ",U" & i & ":V" & i).Copy Worksheets("Monthly CSV").Cells(targetrow, 1 + (i - 5) * 7)

Collectives™ on Stack Overflow

Transpose cells from multiple rows to a single row

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related