Sleeper: How can I automate arranging cell values by repeating and transposing values

[snb]

First: remove 'Option Explicit'
Then run the unpivoting macro:

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion
  ReDim sp(UBound(sn) * (UBound(sn, 2) - 1), 1)
    
  For j = 4 To UBound(sp) - 1
    y = j \ (UBound(sn, 2) - 1) + 1
    sp(j - 4, 0) = sn(y, 1)
    sp(j - 4, 1) = sn(y, j Mod (UBound(sn, 2) - 1) + 2)
  Next
    
  Sheet1.Cells(1, 10).Resize(UBound(sp), 2) = sp
End Sub