I was just wondering why this line of code works:

mySht.Range("A5:A" & endRow).Copy

while this one produces runtime error 1004

mySht.Range(Cells(5, 1), Cells(endRow, 1)).Copy

Regards
Rowan

Try this... You need to be a little more explicit with your references...

mySht.Range(mySht.Cells(5, 1), mySht.Cells(endRow, 1)).Copy

As a guess it can not resolve Cells...

HTH

"Rowan" wrote:

I was just wondering why this line of code works:

mySht.Range("A5:A" & endRow).Copy

while this one produces runtime error 1004

mySht.Range(Cells(5, 1), Cells(endRow, 1)).Copy

Regards
Rowan

Probably because mysht is not the active sheet and the Cells property points
to the active sheet. Try

mySht.Range(mySht.Cells(5, 1), mySht.Cells(endRow, 1)).Copy


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rowan" wrote in message
...
I was just wondering why this line of code works:

mySht.Range("A5:A" & endRow).Copy

while this one produces runtime error 1004

mySht.Range(Cells(5, 1), Cells(endRow, 1)).Copy

Regards
Rowan

Thanks Jim, Bob

I was missing the fact that Cells refers to the activesheet only unless
referenced otherwise.

Regards
Rowan

"Rowan" wrote:

I was just wondering why this line of code works:

mySht.Range("A5:A" & endRow).Copy

while this one produces runtime error 1004

mySht.Range(Cells(5, 1), Cells(endRow, 1)).Copy

Regards
Rowan

Rowan,

Just for interest, to avoid repetition of the mySht object, make it more
efficient, and more readable, I would code it as

With mySht
.Range(.Cells(5, 1), .Cells(endRow, 1)).Copy
'... rest of code
End With

Note the dots before Range and Cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rowan" wrote in message
...
Thanks Jim, Bob

I was missing the fact that Cells refers to the activesheet only unless
referenced otherwise.

Regards
Rowan

"Rowan" wrote:

I was just wondering why this line of code works:

mySht.Range("A5:A" & endRow).Copy

while this one produces runtime error 1004

mySht.Range(Cells(5, 1), Cells(endRow, 1)).Copy

Regards
Rowan