Thanks so much Bernie. But, I'm getting an error "Type Mismatch" and the
debugger points to this line...

If myCell(2, 1).Value < "" Then

"Bernie Deitrick" wrote:

Dim myCell As Range
Set myCell = Range("B:B").Find(What:="#N/A", LookIn:=xlValues)
If myCell Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
If myCell(2, 1).Value < "" Then
Range(myCell, myCell.End(xlDown)).EntireRow.Copy
Else
myCell.EntireRow.Copy
End If


Then use code to paste in the other workbook....

HTH,
Bernie
MS Excel MVP


"Kell2604" wrote in message
...
Hi guys,

I'm trying to get a macro which will find the first cell in column B which
contains #N/A. I have a macro which already does some calculations and sorts
by column B and so puts all of my #N/A's together at the bottom. Also, this
row is always changing, it could be row 110 one day and 127 the next. Once
it finds the first #N/A (in column B row 242 for example) I need it to copy
all of the data in the remaining rows (Row 242 - 263 for example) and paste
into another worksheet.

Hope this makes sense...thanks so much for your help!
Kelley