error 1004

Try replaxcing all that code with this and see if it does what you want.

Option Explicit

Sub x()
    Dim iRow        As Long
    Dim asBuf()     As String

    Worksheets("Data").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "DATA1"

    For iRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        With Cells(iRow, "A")
            If InStr(.Text, ",") Then
                asBuf = Split(Replace(.Text, " ", ""), ",")
                .Offset(1).Resize(UBound(asBuf)).EntireRow.Insert
                With .Resize(UBound(asBuf) + 1)
                    .Value = WorksheetFunction.Transpose(asBuf)
                    .Offset(, 1).Value = Cells(iRow, "B").Value
                End With
            End If
        End With
    Next iRow
    
    With ActiveSheet.UsedRange
        .Value = .Value
        .EntireColumn.AutoFit
    End With
End Sub