Copy and paste cell value if match is found.

[kofafa]

Greetings,

I am currently working on a workbook that consists of 8 worksheets. I am seeking assistance in creating a code for a button that performs the following task: compares the values of column C in the Summary sheet to the values of column B in the other sheets. If there is a match, then the corresponding values of column E, H, I, and J should be copied and pasted in the summary sheet at the corresponding rows.

Thank you for your help.

FRUITS.xlsm

 

[kevin9999]

Please try the following on a copy of your workbook. Please note in your sample file/data you have a space between the "E" and the dash in "APPLE -2", therefore it won't match any values in the APPLE sheet (not sure if that was deliberate on your part or merely a typo?).

Option Explicit
Sub kofafa()
    Dim ws1 As Worksheet, ws As Worksheet
    Set ws1 = Worksheets("SUMMARY")
    Dim a, b, i As Long, j As Long, k As Long
    
    a = ws1.Range("C8:L" & ws1.Cells(Rows.Count, "C").End(xlUp).Row)
    For i = 1 To UBound(a, 1)
        For j = 1 To Worksheets.Count
            If Worksheets(j).Name <> "SUMMARY" Then
                b = Worksheets(j).Range("B7:J" & Worksheets(j).Cells(Rows.Count, "B").End(xlUp).Row)
                For k = 1 To UBound(b, 1)
                    If b(k, 1) = a(i, 1) Then
                        a(i, 7) = b(k, 4): a(i, 8) = b(k, 7): a(i, 9) = b(k, 8): a(i, 10) = b(k, 9)
                    End If
                Next k
                Set b = Nothing
            End If
        Next j
    Next i
    ws1.Range("C8").Resize(UBound(a, 1), UBound(a, 2)).Value = a
    
End Sub

 

[kofafa]

Please try the following on a copy of your workbook. Please note in your sample file/data you have a space between the "E" and the dash in "APPLE -2", therefore it won't match any values in the APPLE sheet (not sure if that was deliberate on your part or merely a typo?).

Option Explicit
Sub kofafa()
    Dim ws1 As Worksheet, ws As Worksheet
    Set ws1 = Worksheets("SUMMARY")
    Dim a, b, i As Long, j As Long, k As Long
   
    a = ws1.Range("C8:L" & ws1.Cells(Rows.Count, "C").End(xlUp).Row)
    For i = 1 To UBound(a, 1)
        For j = 1 To Worksheets.Count
            If Worksheets(j).Name <> "SUMMARY" Then
                b = Worksheets(j).Range("B7:J" & Worksheets(j).Cells(Rows.Count, "B").End(xlUp).Row)
                For k = 1 To UBound(b, 1)
                    If b(k, 1) = a(i, 1) Then
                        a(i, 7) = b(k, 4): a(i, 8) = b(k, 7): a(i, 9) = b(k, 8): a(i, 10) = b(k, 9)
                    End If
                Next k
                Set b = Nothing
            End If
        Next j
    Next i
    ws1.Range("C8").Resize(UBound(a, 1), UBound(a, 2)).Value = a
   
End Sub

You are the Best Kevin!!! thank you so much!! Much appreciated!

 

[kevin9999]

You are the Best Kevin!!! thank you so much!! Much appreciated!

Happy to help, and thanks for the feedback