Why Copy/Paste fails using Offset & Resize of myRange?
 

2003


Copy/Paste is usually simple. That said, I am doing something wrong below.

The following code is excerpted:

Dim myRange as Range, TopCell as Range, TopCell as Range
wks as worksheet
MyColumnsToProcess as long


Note: myRange is on Sheets(1)

myRange = wks.Range(TopCell, BotCell).Resize(, MyColumnsToProcess)

myRange.Offset(0, 5).Resize(, 1).Copy ' attempt to disect a "column" in myRange for the Paste next

Sheets("1200").Range("A" & myRow).Paste

The error is # 438 Object doesn't support this property or method.

Thoughts?

TIA EagleOne

Why Copy/Paste fails using Offset & Resize of myRange?
 

In ,
spake thusly:

Sheets("1200").Range("A" & myRow).Paste

The error is # 438 Object doesn't support this property or
method.

I'm just going to make a guess, based on a similar problem I
had recently. It could be that what I'm describing doesn't
apply to your situation. You'll tell us, I'm sure. :-)

It turns out that if there are array formulas present and
you copy a row, you can't paste it back over the same row.
(At least, this was so for me with Excel 2002.)

E.g., I copied Row 1 and tried to paste it to Rows 1-100
and got that same error. However, I was able to paste
to Rows 2-100. So that's how I got around the problem.

=dman=

Why Copy/Paste fails using Offset & Resize of myRange?
 

Thanks for your time and thoughts.

In this case, the Copy then Paste involve different sheets so I do not believe that is the problem.

EagleOne

Dallman Ross <dman@localhost. wrote:

In ,
spake thusly:

Sheets("1200").Range("A" & myRow).Paste

The error is # 438 Object doesn't support this property or
method.

I'm just going to make a guess, based on a similar problem I
had recently. It could be that what I'm describing doesn't
apply to your situation. You'll tell us, I'm sure. :-)

It turns out that if there are array formulas present and
you copy a row, you can't paste it back over the same row.
(At least, this was so for me with Excel 2002.)

E.g., I copied Row 1 and tried to paste it to Rows 1-100
and got that same error. However, I was able to paste
to Rows 2-100. So that's how I got around the problem.

=dman=

Why Copy/Paste fails using Offset & Resize of myRange?
 

Got it!

myRange.Offset(0, 5).Resize(, 1).Copy Sheets("1200").Range("A" & myRow)


Thanks for any who spent time.

wrote:

myRange.Offset(0, 5).Resize(, 1).Copy ' attempt to disect a "column" in myRange for the Paste next

Sheets("1200").Range("A" & myRow).Paste