Excel explode rows split rows based on a column vba script

explode_rows.vba

Sub Main()
    Call explode_rows("Sheet1", "Sheet2", 2, ",")
End Sub


Function explode_rows(sheet_src As String, sheet_dest As String, col As Integer, sep As String)

    Dim wb As Workbook
    Dim r_src As Integer
    Dim r_dest As Integer
    Dim str_array() As String
    Dim x As Integer
    Dim cell As Excel.Range
        
    Set wb = ActiveWorkbook
    
    r_dest = 1
    ' Set numrows = number of rows of data.
    NumRows = wb.Sheets(sheet_src).Range("A1", Range("A1").End(xlDown)).Rows.Count
    ' Select cell a1.
    ' wb.Sheets(sheet_src).Range("A1").Select
    ' Establish "For" loop to loop "numrows" number of times.
    For r_src = 1 To NumRows
       Set cell = wb.Sheets(sheet_src).Cells(r_src, col)
       str_array = Split(cell.Value2, sep)
       
       ' str_count = UBound(str_array, 1) - LBound(str_array, 1) + 1
       Dim str As Variant
       
       wb.Sheets(sheet_src).Rows(r_src).EntireRow.Copy
        For Each str In str_array
            wb.Sheets(sheet_dest).Cells(r_dest, 1).PasteSpecial xlPasteValues
            
            ' fix the split column
            wb.Sheets(sheet_dest).Cells(r_dest, col).Value = CStr(str)
            
            r_dest = r_dest + 1
        Next str
       
       ' Selects cell down 1 row from active cell.
       ' ActiveCell.Offset(1, 0).Select
    Next

End Function

sample_input.csv

sample_output.csv