Excel VBA copy paste loop from one sheet to another when condition is met

Question

I'm a beginner in VBA. I already checked if the question was asked already but found nothing that fits perfectly and also I failed to modify properly the closest codes I found.

I have a file with raw sales data (around 5,000 items) located in sheet1 column A. In sheet2, I have a table with around 500 data in column A where the duplicated items from the sheet1 have been eliminated. Each of the 500 items were renamed in column B.

I want to create a loop which check for each cell in sheet1 column A if the data is same with one of the data from sheet2 column A. When the condition is met, it then copy paste the adequate cell in column B from sheet2 in the adequate cell in sheet1 column B (i.e. same row). I need the operation to be done for the 500 items.

• Example Sheet 1 (see link: https://1drv.ms/i/s!AiRwDFaSvBNalTb0JOIkq8KxiBKs / expected results in red)

Product Sorting 1

choco
cosme
choco
cosme
cosme
cosme
cosme
choco
choco
cosme
cosme
jam cosme
cosme

• Example Sheet2 (see link: https://1drv.ms/i/s!AiRwDFaSvBNalTQlKgDKEJTNLvOU)

Product

choco cosme jam

Which refers to column Sorting2 with:

CHO COS JAM

• Code written so far

  Sub sorting()
  
   Dim Cell As Range
   Dim i As Integer
  
    For Each Cell In Sheet1.Range("A2:A10000)
     If Cell.Value = Sheet2.Range("A2") Then
     Sheets("Sheet2").Select
     Range("A2").Select
     Selection.copy
     Sheets("Sheet1").Select
     Cell(i,2).Select
     Cell.PasteSpecial
     End If
    Next Cell
  
  End Sub
  

Answer 1

This sounds like you just need a vlookup formula in sheet1 column b

Something like in cell B2 sheet1

=Vlookup(A2,sheet2!'A1:B500,2,0)

Then copy paste it all the way down column b

Looking at you code you aren't setting your variable i to anything, looks like you need to add i=i+1 at the before your if

I would also remove your select and selection as this can cause error if you click you mouse. You just need

Sheets("sheet2").Range("A2"). copy

Instead of

    Sheets("sheet2").select
Range("A2").select
    Selection.copy

Also shouldn't you be copying B2 as A2 is the value you are matching