Macro to find SKU in column; then copy data in row and paste special (values)

Hi all

I'm trying to do the following:

I have a list of SKU's in column D, descriptions in column E and data in Column F and G

I want to build a macro that finds a SKU that I punch in to a cell (D1), to then search the column of D (2:58) for this SKU, and copy the data from Column G (Which is a vlookup) and 'paste special values' into Column F.

The SKU in cell D1 will change, which is why it will need to probably index search through the column of D, but I've tried a number of times and simply cannot get it to work.

Any help would be wonderful

Starkey

Hi Starkey,

Do you really need a macro as won't this formula in cell F1 do the job?:

=IFERROR(VLOOKUP(D1,$D$2:$G$58,4,FALSE),"")

Regards,

Robert

Could you please upload a workbook with a before and after scenario (devoid of all sensitive information) and I'll see what I can do.

Hi Starkey,

Try this:

Option Explicit
Sub Macro1()

    'http://www.excelforum.com/excel-programming-vba-macros/859746-macro-to-find-sku-in-column-then-copy-data-in-row-and-paste-special-values.html?p=2928721

    Dim wstSheet1 As Worksheet, _
        wstSheet2 As Worksheet
    Dim rngFoundCell As Range
    Dim lngMatchRow1 As Long, _
        lngMatchRow2 As Long
    
    Application.ScreenUpdating = False
    
    Set wstSheet1 = Sheets("Before")
    Set wstSheet2 = Sheets("Data")
    
    If Len(wstSheet1.Range("C2")) = 0 Then
        MsgBox "There is no entry in cell C2 in sheet """ & wstSheet1.Name & """ to search on." & vbNewLine & "Enter one and try again.", vbExclamation, "My Data Match Editor"
        Exit Sub
    End If
    
    Set rngFoundCell = wstSheet1.Range("B5:B" & wstSheet1.Cells(Rows.Count, "B").End(xlUp).Row).Find(What:=wstSheet1.Range("C2").Value)
                                                            
    If Not rngFoundCell Is Nothing Then
        lngMatchRow1 = rngFoundCell.Row
        Set rngFoundCell = Nothing
    Else
        MsgBox "There is no matching entry for """ & wstSheet1.Range("C2") & """ in Col. B of sheet """ & wstSheet1.Name & """.", vbInformation, "My Data Match Editor"
        Set wstSheet1 = Nothing: Set wstSheet2 = Nothing: Set rngFoundCell = Nothing
        Exit Sub
    End If
    
    Set rngFoundCell = wstSheet2.Range("B5:B" & wstSheet2.Cells(Rows.Count, "B").End(xlUp).Row).Find(What:=wstSheet1.Range("C2").Value)
    
    If Not rngFoundCell Is Nothing Then
        lngMatchRow2 = rngFoundCell.Row
        Set rngFoundCell = Nothing
    Else
        MsgBox "There is no matching entry for """ & wstSheet1.Range("C2") & """ in Col. B of sheet """ & wstSheet2.Name & """.", vbInformation, "My Data Match Editor"
        Set wstSheet1 = Nothing: Set wstSheet2 = Nothing: Set rngFoundCell = Nothing
        Exit Sub
    End If
    
    wstSheet1.Range("D" & lngMatchRow1 & ":G" & lngMatchRow1).Value = wstSheet2.Range("D" & lngMatchRow2 & ":G" & lngMatchRow2).Value
    
    Application.ScreenUpdating = True
    
    MsgBox "The matching data from tab """ & wstSheet2.Name & """ has now been imported to sheet """ & wstSheet1.Name & """.", vbInformation, "My Data Match Editor"
    
    Set wstSheet1 = Nothing: Set wstSheet2 = Nothing

End Sub

Regards,

Robert

EDIT

Hi Rob, no matter. I worked through your code and discovered how to adjust it accordingly.

Thank you so much again, this will be really helpful!

Starkey

---

Hi Robert,

Sorry if I'm pushing my luck here, but is it also possible to pull the data from just the 'Before' sheet? In essence, the 'Data' tab is pulling from a number of different sheets in my spreadsheet, so that's not a viable option ongoing.

Cheers,

Starkey

Hi Starkey,

I'm glad to have helped and thanks for marking the thread as solved (and for adding to my reputation )

Kind regards,

Robert