Compare 2 sheets and copy matching data

[Sarah]

I have 2 sheets: Agents and Production. I want to compare the data in
column B of Agents to column B of Production. If the value from Agents
matches a row in Production, I want to copy the value from column O of
that row of Production to another worksheet (Count). It's been a while
since I've done any VBA programming, and I was never very good at it
to begin with. Can anyone help me?

Sarah

 

[Guest]

copy the values of sheet Production, Column B to column A of Count

in column B of Count put the formula
B2:
=if(countif(Agents!$B:$B,A2)>0,Production!O2,"")

then drag fill down the column.

 

[Guest]

I just threw this together. Try it out, mess with it for your needs.

Sub test()
Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
Dim cnt1 As Integer, x As Integer

Set sht1 = Worksheets("Agents")
Set sht2 = Worksheets("Production")
Set sht3 = Worksheets("Count")

sht1.Activate

cnt1 = sht1.Range(Range("B1"), Range("B1").End(xlDown)).Count

Range("B1").Activate
For x = 1 To cnt1
If sht1.Range("B1").Offset(x, 0).Value = sht2.Range("B1").Offset(x,
0).Value Then
sht1.Range("B1").Offset(x, 13).Copy
sht3.Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial
(xlPasteValues)
End If
Next x

End Sub

 

[Sarah]

I just threw this together. Try it out, mess with it for your needs.

Sub test()
Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
Dim cnt1 As Integer, x As Integer

Set sht1 = Worksheets("Agents")
Set sht2 = Worksheets("Production")
Set sht3 = Worksheets("Count")

sht1.Activate

cnt1 = sht1.Range(Range("B1"), Range("B1").End(xlDown)).Count

Range("B1").Activate
For x = 1 To cnt1
If sht1.Range("B1").Offset(x, 0).Value = sht2.Range("B1").Offset(x,
0).Value Then
sht1.Range("B1").Offset(x, 13).Copy
sht3.Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial
(xlPasteValues)
End If
Next x

End Sub

Okay, maybe I just don't understand the code, but this doesn't seem to
copy the value from sheet Production to sheet Count in the row on
Production that matches a value on Agents.

I have Agent numbers on the Agent sheet in column B.
I have production reporting on the Production sheet. The column with
the agent numbers here is also B. The column with the policy count in
which I'm interested varies (unfortunately), but I could manually
paste it into column C.

I want this code to match values from column B of Agent to column B of
Production. When the matching row is located, I want it to copy the
value from that row of column C of Production to sheet Count (column A
would be fine).