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

[EagleOne]

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

 

[Dallman Ross]

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=

 

[EagleOne]

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

 

[EagleOne]

Got it!

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


Thanks for any who spent time.