Paste method, defining the range

[bettingman]

Hi,

I am using the Paste method to paste data from the Clipboard into a range. It works fine when the range is defined like this:

Please Login or Register  to view this content.

It results in an error however when I use the following code:

Please Login or Register  to view this content.

Any ideas why?

Thanks

[Paul]

Hi Bettingman, welcome to the forum.

The issue is that "Cells(1, i - 1)" and "Cells(100, i + 2)" don't reference which worksheet you're using. Even though it's inside the WOrksheets("Sheet2").Range() qualifier, you still need to specify the worksheet for those Cells() references.

Please Login or Register  to view this content.

Or, more readable:

Please Login or Register  to view this content.

By specifying the destination after the Copy command, you don't need to put it on a separate line. You also only need to specify the first (top left) cell in the range where you want the data pasted.

Hope that helps!

[MarvinP]

Hi bettingman and welcome to the forum.

GREAT QUESTION. This has fooled me many times. I also like Paul's answer but need to study and learn this syntax better.

Would you try something for me. Put a period in front of the cell and see if that works. Let me know
Use

Please Login or Register  to view this content.

Using the With Construct I've learned you need to do stuff like

Please Login or Register  to view this content.

Let me know how to remember using periods or not.

[vlady]

@ MarvinP tried it in 2003

Compile error in .cells

Error message

Compile error
invalid or unqualified reference

[Paul]

Adding periods only works when you use With/End With around that code.

Please Login or Register  to view this content.

is also:

Please Login or Register  to view this content.

You can use a "." before any object belonging to Sheet2 within the With/EndWith construct to make your code shorter and more readable.

[MarvinP]

Ok then,

How about this - will it work?

Please Login or Register  to view this content.

[Paul]

It should, Marvin (assuming you copied something in a previous line, of course).

[bettingman]

Thanks guys, very helpful!

Sorry but I had to go away and couldn't respond earlier.