must activate sheet before paste?

[big_bear]

Hi all, this is quite a basic question really that I've avoided answering by just using clunky programming, but making two unnecessary sheet activations for thousands of lines of data is starting to become a problem!

Why does this work:
Range(Cells(i, 1), Cells(i, 19)).Copy
Sheets("07s ABCC").select
Cells(rownum, 1).select
activesheet.paste


but this doesn't?
Range(Cells(i, 1), Cells(i, 19)).Copy Sheets("07s ABCC").Range(Cells(rownum, 1), Cells(rownum, 19))

To help, i is the integer I'm using in a loop to check certain aspects of each row (1-19) in my first sheet, rownum is just
sheets("07s ABCC").range("A1").end(xldown).row + 1

I know that activating the sheet and going back each time is very inefficient, but I don't know why the destination won't work!

[Bob Phillips]

See if this works



Range(Cells(i, 1), Cells(i, 19)).Copy Sheets("07s ABCC").Range(Sheets("07s ABCC").Cells(rownum, 1), Sheets("07s ABCC").Cells(rownum, 19))

[big_bear]

You're a genius, could you indulge me by telling me why this works?
Also, impressive speed xld.

[Bob Phillips]

Because you were not qualify all of the properties of the destination range.

You qualified the Range object with its parent sheet, but by not qualifying the Cells properties of that range with the same parent sheet, VBA was looking at the activesheet for those cells, that is the source worksheet.

Just happened I was watching the board as I was in a discussion with Doug, so I saw it almost immediately.