Sub Вставить_артикулы()
    Dim shRes As Worksheet, arrResA(), arrResC(), shSrc As Worksheet
    Dim var, r As Long, lr As Long, i As Long
    
    Set shRes = Worksheets("Моя таблица")
    Set shSrc = Worksheets("Прайс поставщика")
    lr = shRes.Cells(shRes.Rows.Count, "A").End(xlUp).Row
    arrResA() = shRes.Range("A2:A" & lr).Value
    ReDim arrResC(1 To UBound(arrResA), 1 To 1)
    
    On Error Resume Next
    For i = 1 To UBound(arrResA)
        If arrResA(i, 1) <> "" Then
            var = WorksheetFunction.Trim(arrResA(i, 1))
            var = Split(var, " ")
            var = var(UBound(var))
            r = 0
            r = WorksheetFunction.Match(var, shSrc.Columns("A"), 0)
            If r <> 0 Then
                arrResC(i, 1) = var
            End If
        End If
    Next i
    On Error GoTo 0
    
    shRes.Range("C2:C" & UBound(arrResC)).Value = arrResC()
    MsgBox "Готово!", vbInformation
End Sub