Combine Data in Mutiple Columns into Rows

[maytey]

My file has multiple columns (Attached is only a sample) and I want to convert the data from column c onwards into the rows.
Capture.jpg
The results will be as follows:
Capture2.jpg

Can someone please help me with a VBA macro to combine the multiple cloumn data? THanks Much!

[georgiboy]

Maybe something like:

Sub ReOrder()     
    Dim rCell As Range, x As Long, endCol As Long
    Dim endRow As Long, newRow As Long
    
    endRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row ' set your end row of data
    endCol = Range("AU:AU").Column ' set your end column of data
    newRow = 2 ' first row of sheet 2
     
    With Sheet2
        For x = 1 To endCol - 2
            For Each rCell In Sheet1.Range("B2:B" & endRow).Cells
                .Cells(newRow, 1).Value = rCell.Offset(, -1).Value
                .Cells(newRow, 2).Value = rCell.Value
                .Cells(newRow, 3).Value = rCell.Offset(, x).Value
                .Cells(newRow, 4).Value = Sheet1.Cells(1, rCell.Offset(, x).Column).Value
                newRow = newRow + 1
            Next rCell
        Next x
    End With
    
End Sub

It restructures the data onto sheet2

Hope this helps

[mana]

Option Explicit


Sub test()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Range
    Dim i As Long
    Dim n As Long
    
    Set ws1 = Worksheets("Sept-P2")
    Set ws2 = Worksheets("Combined")
    
    Set r = ws1.Cells(1).CurrentRegion
    Set r = Intersect(r, r.Offset(1))
    
    n = r.Rows.Count
    
    ws1.Cells(1).Resize(, 2).Copy ws2.Cells(1)
    
    For i = 3 To r.Columns.Count
        With ws2.Cells(Rows.Count, "a").End(xlUp)
            r.Columns("a:b").Copy .Offset(1)
            r.Columns(i).Copy .Offset(1, 2)
            r.Columns(i).Cells(0).Copy .Offset(1, 3).Resize(n)
        End With
    Next
    
End Sub