Unpivot data macro

Try this...

Sub Transposer()
    
    Dim v As Variant, Headers As Variant, c As Long, r As Long, i As Long
    
    With Sheets("Data").Range("A1").CurrentRegion
        Headers = .Rows(1).Value
        v = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    
    Application.ScreenUpdating = False
    
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A1:D1").Value = Array("Material", "Plant", "Qty", "Month-Year")
    Columns("D").NumberFormat = "mmm-yy"
    
    i = UBound(v, 1)
    r = 2   'start row
    
    For c = 3 To UBound(v, 2)
        Range("A" & r).Resize(i).Value = Application.Index(v, 0, 1)
        Range("B" & r).Resize(i).Value = Application.Index(v, 0, 2)
        Range("C" & r).Resize(i).Value = Application.Index(v, 0, c)
        Range("D" & r).Resize(i).Value = Headers(1, c)
        r = r + i
    Next c
    
    Application.ScreenUpdating = True
    
End Sub