Tom

Many thanks for this, with a little tweak or two it now works fine.

However I now have another problem, it is possible for the same eartag to
appear twice on Sheet2. I want the value in column P of both rows to appear
in column P on Sheet1.

For example, UK F2611 00231 may appear twice on Sheet2 with a 'C' and an 'L'
in column P, I would like 'CL' to be displayed in column P on Sheet1 for UK
F2611 00231.

Can Lookup look for the same value twice?

Gareth

This is the code as it is now:

Sub InsertBandCorL()
Application.ScreenUpdating = False
With Worksheets("Cattle Details")
Set rng = .Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
With Worksheets("Retention Periods")
Set rng1 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng
res = Empty
res = Application.VLookup(cell.Value, rng1.Resize(, 16), 2, 0)
If Not IsError(res) Then
cell.Offset(0, 5).Value = res
cell.Offset(0, 13).Value = Application.VLookup(cell.Value, rng1.Resize(,
16), 16, 0)
End If
Next
Application.ScreenUpdating = True
End Sub

"Tom Ogilvy" wrote in message
...
The worksheet formula wouldn't work in Column H if you already have data
there and want to concatenate to that data unless you want to use a dummy
column, then paste back over column H. This macro will append the data
from
column B to Column H. It assumes the eartag value is in column A on each
sheet and values start in row 2.

Sub Tester1()

With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
With Worksheets("Sheet2")
Set rng1 = .Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng
res = Empty
res = Application.VLookup(cell.Value, _
rng1.Resize(, 16), 2, 0)
If Not IsError(res) Then
cell.Offset(0, 7).Value = _
cell.Offset(0, 7).Value & " " & res
cell.Offset(0, 15).Value = _
Application.VLookup(cell.Value, _
rng1.Resize(, 16), 16, 0)
End If
Next
End Sub

Make a backup of your workbook before running the macro - test on the
backup
copy.

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
I have 2 sheets in my file.

The first contains data from database1 and is made up of all cattle on a
farm at present and those which have moved off in the last 3 yeras.

The second contains data from database2 and is made up of cattle that
subsidies have been claimed on in the last 3 years.

Sheet1 may have thousands of records where Sheet2 will have a maximum of
a
few hundred.

The only common field is the animals Eartag. What I want to do is get
information from 2 columns on Sheet2 and place it in Sheet1.

The locations of the columns are as follows:

I want column B on Sheet2 to go into column H on Sheet1 (Column H
already
has data in it).

Then I would like column P on Sheet2 to go into column P on Sheet1(this
column is empty).

Any suggestions gratefully received.

Gareth