IF those unique values in column A match between the sheets, you could use:

=NOT(ISERROR(MATCH(A2,Sheet2!A:A,FALSE)))

(assuming you have a header row)

and copy down to match your dataset. Then sort based on that column, and copy the rows where the
value is TRUE. This can be macro-ized if you need... For example, to put it into Column I of
Sheet1, which will move the rows with key values matched to the top of the sheet for easy copying -
which can also be part of a macro:

Sub TryNow()
Dim myCopy As Range
With Worksheets("Sheet1")
.Range("I2", .Range("H65536").End(xlUp)(1, 2)).Formula _
= "=NOT(ISERROR(MATCH(A2,Sheet2!A:A,FALSE)))"
.Range("I2").CurrentRegion.Sort Key1:=.Range("I2"), _
Order1:=xlDescending, Header:=xlYes
Set myCopy = .Columns("I:I").Find(What:="False", After:=.Range("I1"), _
LookIn:=xlValues, LookAt:=xlWhole)
Set myCopy = .Range("A1", myCopy(0, 0))
myCopy.Copy
End With
End Sub

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
Nik, I am looking at your suggestion now and it does seem far easier.
Only thing i am now struggling with is getting it to copy a range of
cells at once.

Bernie, the first column in each sheet holds unique values (these are
unique to each list/sheet but some of those in dataset1 will be in
dataset2).