Copy cell from sheet1 to sheet 2 only if condition met

[i8ur4re]

I have 2 worksheets, sheet1 is the main database with 10 columns and over 9000+ rows, while sheet2 has prices and inventory for over 9000+ items. sheet1 has "ItemNo", so does sheet2, I need to make sure that sheet1 and sheet2 both have that same "ItemNo", if they do it will copy the prices and inventory from sheet2 over to sheet1.

I cant copy paste the prices or inventory because the "ItemNo" are not in a consistent order. It will only copy Inventory and price if "ItemNo" in sheet1 matches "ItemNo" in sheet2.

Below is an example:


Sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD]ItemNo
[/TD]
[TD]Description
[/TD]
[TD]UPC
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD]ItemNo
[/TD]
[TD]Price
[/TD]
[TD]Inventory
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Final Data should look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ItemNo
[/TD]
[TD]Description
[/TD]
[TD]UPC
[/TD]
[TD]Price
[/TD]
[TD]Inventory
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you

 

[Fluff]

How about

Sub CopyInventory()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("Sheet2")
 Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 1).Resize(, 2)
      Next Cl
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 3).Resize(, 2).Value = .Item(Cl.Value).Value
      Next Cl
   End With
End Sub

 

[i8ur4re]

How about

Sub CopyInventory()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("Sheet2")
 Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 1).Resize(, 2)
      Next Cl
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 3).Resize(, 2).Value = .Item(Cl.Value).Value
      Next Cl
   End With
End Sub

My apologies, I just got today's data right after i posted in the thread and noticed, 3 worksheets, the main one i need prices and inventory in (main) and two other worksheets, prices and inventory. All 3 sheets have "ItemNo", I created two new columns in the "main" sheet, prices and inventory, how would i go about doing it with the additional sheet. Thank you in advance for your help.

 

[Fluff]

Can the same itemNo occur on both the other two sheets?
If so what needs to happen?

 

[i8ur4re]

Can the same itemNo occur on both the other two sheets?
If so what needs to happen?

The "ItemNo" appears on all 3 sheets, its like an ID for the products, for example in my case its furniture, so a 100006 is an "ItemNo" that appears in inventory (how many in stock), it appears in the MasterData (upc, descriptions, dimensions, etc) and the Price sheet which is the price for that product.

Unfortunately we get 3 data sheets, as opposed to 1 main database, what I am trying to do is create that master data sheet by combining all the required information for that item, example is 100006, i need the price as well as the inventory so it is placed in that MasterData. Does that make sense, I can link the file if it helps.
https://www.dropbox.com/s/qaa1fton1ig3mx5/CoasterMaster_AE_0426.2.xlsx?dl=0

 

[Fluff]

Thanks for the file.
Try this

Sub CopyInventory()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Ws3 As Worksheet
   
   Set Ws1 = Sheets("Main")
   Set Ws2 = Sheets("Inventory")
   Set Ws3 = Sheets("Price")
 Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      .comparemode = vbTextCompare
      For Each Cl In Ws3.Range("A2", Ws3.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Text) Then .Add Cl.Text, Array(Cl.Offset(, 6).Value, "")
      Next Cl
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Text) Then
            .Item(Cl.Text) = Array(.Item(Cl.Text)(0), Cl.Offset(, 10).Value)
         Else
            .Add Cl.Text, Array("", Cl.Offset(, 10).Value)
         End If
      Next Cl
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Text) Then Cl.Offset(, 2).Resize(, 2).Value = .Item(Cl.Text)
      Next Cl
   End With
End Sub

 

[i8ur4re]

Thanks for the file.
Try this

Sub CopyInventory()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Ws3 As Worksheet
   
   Set Ws1 = Sheets("Main")
   Set Ws2 = Sheets("Inventory")
   Set Ws3 = Sheets("Price")
 Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      .comparemode = vbTextCompare
      For Each Cl In Ws3.Range("A2", Ws3.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Text) Then .Add Cl.Text, Array(Cl.Offset(, 6).Value, "")
      Next Cl
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Text) Then
            .Item(Cl.Text) = Array(.Item(Cl.Text)(0), Cl.Offset(, 10).Value)
         Else
            .Add Cl.Text, Array("", Cl.Offset(, 10).Value)
         End If
      Next Cl
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Text) Then Cl.Offset(, 2).Resize(, 2).Value = .Item(Cl.Text)
      Next Cl
   End With
End Sub

THANK YOU SO MUCH, that worked exactly how i wanted it to. You just saved me a weeks worth of work. Thank you thank you thank you.

 

[Fluff]

Glad to help & thanks for the feedback