Kelley,

Try:

If myCell(2, 1).Text < "" Then
Intersect(Range("C:AE"), Range(myCell, myCell.End(xlDown)).EntireRow).Copy
Else
Intersect(Range("C:AE"), myCell.EntireRow).Copy
End If

And I don't mind being bothered.... ;-)

HTH,
Bernie
MS Excel MVP


"Kell2604" wrote in message
...
I hate to keep bothering you, you have been very helpful. But...this
works,
in that it has moved over 1 column (to B) when grabbing the data. But I
need
it to get to C and I need it to grab all data from C on. When I run this
it
is grabbing column B and 3 other columns. The report has 10 or so
additional
columns. (C - AE)

Any thoughts?

"Bernie Deitrick" wrote:

Change to:

If myCell(2, 1).Text < "" Then
Range(myCell, myCell.End(xlDown).End(xlToRight)).Copy
Else
Range(myCell, myCell.End(xlToRight)).Copy
End If

This will not work if you have blank cells or columns....

HTH,
Bernie
MS Excel MVP


"Kell2604" wrote in message
...
Bernie...I have one last question.

After playing with this I realized that I don't need (want) it to copy
the
entire row. I need it to copy the data from colum C over (C, D, E, F,
etc.).
Is there a way to work this into your code??

Thanks again!!

"Kell2604" wrote:

Great - thanks, that worked!!

"Bernie Deitrick" wrote:

Oops, sorry - forgot that it would be an error value. (That's what
I get for just writing
code....)
Anyway, use

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

HTH,
Bernie
MS Excel MVP


"Kell2604" wrote in message
...
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