if i got ur right then try :

Sub FindAndCopy()
For rw = 1 To 100
If Sheets("Sheet1").Cells(rw, 1) = "What im looking at" Then
Sheets("Sheet1").Range("F" & rw).Copy Sheets("Sheet2").Range("A" &
Cells(65535, "A").End(xlUp).Row + 1) 'copy to colA
Sheets("Sheet1").Range("R" & rw).Copy Sheets("Sheet2").Range("B" &
Cells(65535, "B").End(xlUp).Row + 1) 'copy to colB
End If
Next
End Sub


" skrev:

No Dave ... im NOT trying to find unique entries.
And thanks Excellent for the help ....

My question is that:

Suppose I find my required data in Row 10. And i know that in Row 10 ,
I need to copy the Entries of Column F and Column R. ( which means
Cells F10 and R10)

How will i Select these Cells? Logically I could use

my_row = ActiveCell.Row
and then use Cells( "F" & my_row)
But this does not work.
Please tell me what do i need to write here. I think im only lacking
the correct syntax.

thanks a lot both of you...

Looking forward to your further guidance