VBA - Copying columns, adding rows

check the near solution...
I have used array formula so use ctrl + shift + enter to enter the formula...



Don't forget to click *

For tackling the matches..
I think this can be a better solution...
If you can Product name.. You wanna search then I can show you all the matches..

try this..


Don't forget to click *

I think this is what you want:

Sub MainMacro()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Sheet2")
Dim rCell As Range, rFind As Range

Application.ScreenUpdating = False

For Each rCell In ws2.Range("F2:F" & ws2.Range("F" & Rows.Count).End(xlUp).Row)
    If rCell.Value <> "" Then
        Set rFind = ws1.Range("E1:E" & ws1.Range("E" & Rows.Count).End(xlUp).Row).Find(What:=rCell.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
        If Not rFind Is Nothing Then
            ws1.Range("F" & rFind.Row).Resize(1, 3).Value = ws2.Range("I" & rCell.Row).Resize(1, 3).Value
            ws1.Range("I" & rFind.Row).Value = ws2.Range("M" & rCell.Row).Value
            ws1.Range("J" & rFind.Row).Value = ws2.Range("O" & rCell.Row).Value
            ws1.Range("K" & rFind.Row).Value = ws2.Range("Q" & rCell.Row).Value
            rFind.Offset(1, 0).EntireRow.Insert Shift:=xlDown
            rFind.Offset(0, -3).Resize(2, 4).FillDown
        End If
    End If
Next rCell

Application.ScreenUpdating = True
            
End Sub