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