I've been fooling with this line of code for over an hour. For some
reason, VBA won't accept ".cells" as a property or method or event of
..range today. Getting a "method range of object worksheet failed"
error. Any ideas? VBA help doesn't even recognize "cells" at all, so no
help there. But it is in the Object Browser shown as being ok with a
range and a range being ok with a worksheet.

Set rCopy = wsExtFrom.Range(.Cells(lCopyRow, lExtrFromCol),
..Cells(lCopyRow, lExtrToCol))

I've tried it with and without the periods in front of "cells".

All of the variables show in the "list" of applicable variables. I'm
stumped, again.

Thanks in advance.

You are not in a with statement as far as I can see, and might not have
that page activated, so try this:
Set rCopy = wsExtFrom.Range(wsExtFrom.Cells(lCopyRow, lExtrFromCol), _
wsExtFrom.Cells(lCopyRow, lExtrToCol))

HTH

Charles Chickering

davegb wrote:
I've been fooling with this line of code for over an hour. For some
reason, VBA won't accept ".cells" as a property or method or event of
.range today. Getting a "method range of object worksheet failed"
error. Any ideas? VBA help doesn't even recognize "cells" at all, so no
help there. But it is in the Object Browser shown as being ok with a
range and a range being ok with a worksheet.

Set rCopy = wsExtFrom.Range(.Cells(lCopyRow, lExtrFromCol),
.Cells(lCopyRow, lExtrToCol))

I've tried it with and without the periods in front of "cells".

All of the variables show in the "list" of applicable variables. I'm
stumped, again.

Thanks in advance.

Die_Another_Day wrote:
You are not in a with statement as far as I can see, and might not have
that page activated, so try this:
Set rCopy = wsExtFrom.Range(wsExtFrom.Cells(lCopyRow, lExtrFromCol), _
wsExtFrom.Cells(lCopyRow, lExtrToCol))

HTH

Charles Chickering

I put it inside a with statement and deleted the "wsExtrFrom" after the
= sign, and it ran! Don't think I'll ever figure out why or when it
does that.

Thanks for the help!


davegb wrote:
I've been fooling with this line of code for over an hour. For some
reason, VBA won't accept ".cells" as a property or method or event of
.range today. Getting a "method range of object worksheet failed"
error. Any ideas? VBA help doesn't even recognize "cells" at all, so no
help there. But it is in the Object Browser shown as being ok with a
range and a range being ok with a worksheet.

Set rCopy = wsExtFrom.Range(.Cells(lCopyRow, lExtrFromCol),
.Cells(lCopyRow, lExtrToCol))

I've tried it with and without the periods in front of "cells".

All of the variables show in the "list" of applicable variables. I'm
stumped, again.

Thanks in advance.

With wsExtFrom
Set rCopy = .Range(.Cells(lCopyRow, lExtrFromCol), _
.Cells(lCopyRow, lExtrToCol))
End with
msgbox rcopy.Address(0,0,xla1,true)

--
Regards,
Tom Ogilvy


"davegb" wrote:

I've been fooling with this line of code for over an hour. For some
reason, VBA won't accept ".cells" as a property or method or event of
..range today. Getting a "method range of object worksheet failed"
error. Any ideas? VBA help doesn't even recognize "cells" at all, so no
help there. But it is in the Object Browser shown as being ok with a
range and a range being ok with a worksheet.

Set rCopy = wsExtFrom.Range(.Cells(lCopyRow, lExtrFromCol),
..Cells(lCopyRow, lExtrToCol))

I've tried it with and without the periods in front of "cells".

All of the variables show in the "list" of applicable variables. I'm
stumped, again.

Thanks in advance.

Tom Ogilvy wrote:
With wsExtFrom
Set rCopy = .Range(.Cells(lCopyRow, lExtrFromCol), _
.Cells(lCopyRow, lExtrToCol))
End with
msgbox rcopy.Address(0,0,xla1,true)

Sorry, Tom, didn't see your reply until today.

Got the with statement needing to be there. What is the "msgbox
rcopy.address(0,0,xla1, true) for?


--
Regards,
Tom Ogilvy


"davegb" wrote:

I've been fooling with this line of code for over an hour. For some
reason, VBA won't accept ".cells" as a property or method or event of
..range today. Getting a "method range of object worksheet failed"
error. Any ideas? VBA help doesn't even recognize "cells" at all, so no
help there. But it is in the Object Browser shown as being ok with a
range and a range being ok with a worksheet.

Set rCopy = wsExtFrom.Range(.Cells(lCopyRow, lExtrFromCol),
..Cells(lCopyRow, lExtrToCol))

I've tried it with and without the periods in front of "cells".

All of the variables show in the "list" of applicable variables. I'm
stumped, again.

Thanks in advance.