Untitled

Sub TransformData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentProductCode As String
    Dim currentProductName As String
    
    ' Define the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Find the last row of data in the sheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row starting from row 9
    For i = 9 To lastRow
        ' Check if there's a new product code and name in columns D and E
        If ws.Cells(i, 4).Value <> "" Then
            currentProductCode = ws.Cells(i, 4).Value
            currentProductName = ws.Cells(i, 5).Value
        End If
        
        ' Insert the product code and name in the new columns to the left of date and time
        ws.Cells(i, 1).EntireColumn.Insert
        ws.Cells(i, 1).Value = currentProductCode
        ws.Cells(i, 1).Offset(0, 1).Value = currentProductName
    Next i
End Sub