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

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.

--
Regards,
Tom Ogilvy



"Sarah" wrote:

> 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
>
>

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" wrote:

> 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
>
>

On Jun 29, 11:12 am, AKphidelt <AKphid...@discussions.microsoft.com>
wrote:
> 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).