Match data from 2 sheets, copy from one to the other.....

[buzz71023]

I have a couple spread sheets that have data as follows

Sheet 1 – Column A through U (row 5 first entry with data w/ no defined amount of rows, could be thousands)
Sheet 2 – Column A through T (row 5 first entry with data w/ no defined amount of rows, could be thousands)

I need help building a code that will look at each entry in column B on sheet 1, find the matching entry on sheet 2 in column D, extract(copy) the entry from column P of that row(from sheet 2) and paste it back to the matching entry row on sheet1 column V

For instance. If my first entry in column B on sheet 1 was 123456. Sheet 2 that same data may be 2,000 row further down but with a $ amount in column P that needs to be copied to sheet1.
The code would need to take 123456 from sheet 1 find it in Column D of sheet 2, take the dollar amount from column P on sheet 2 and paste it back on the row with 123456 on sheet 1 in column V.

Hope this was clear enough and not too confusing. If I need to clarify more I will be happy to.

All feedback is welcome and is much appreciated. Thanks in advance

 

[Fluff]

How about

Sub CopyData()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws2.Range("D2", Ws2.Range("D" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 12)
      Next Cl
      For Each Cl In Ws1.Range("B2", Ws1.Range("B" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 20).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[buzz71023]

Thank you for the quick response
My apologies as well as I was actually given some bad info. It may not change the code much or whether it would work or not.
I tried to replace the correct columns with what I initially put but the code didn't work.

Correct info below.
take each entry in sheet1 column B and look for entry in sheet 2 column A. Take the value from column S on sheet2 and paste in sheet1 column V of the original row from the first step.

 

[Fluff]

Ok, use this

Sub CopyData()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws2.Range("[COLOR=#ff0000]A2[/COLOR]", Ws2.Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, [COLOR=#ff0000]18[/COLOR])
      Next Cl
      For Each Cl In Ws1.Range("B2", Ws1.Range("B" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 20).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[buzz71023]

ok the code did work but the problem was it skipped all the cells on Sheet 1 that had values and placed "15051818" in column V on the blank cells in column B.
Could it be something with recognizing the data in the cell since this was exported from another data base?

 

[Fluff]

This should deal with the blank cells

Sub CopyData()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   
   With CreateObject("scripting.dictionary")
      .CompareMode = vbTextCompare
      For Each Cl In Ws2.Range("A5", Ws2.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) And Cl.Value <> "" Then .Add Cl.Value, Cl.Offset(, 18)
      Next Cl
      For Each Cl In Ws1.Range("B5", Ws1.Range("B" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 20).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

However, if it skipped all the values on sheet1, then those values probably don't exist on sheet2.

 

[buzz71023]

Works great.
I actually noticed the green triangle in the top left and had to convert those to "numbers" before it would work.
Thank you again!!! This will keep from someone having to go through about 40,000 entries and moving each over.

 

[Fluff]

Glad to help & thanks for the feedback