VBA Copy Range(Cells) Paste Range(Cells) doesn't work?

jmpmolegraaf

New Member
Joined
Jul 10, 2013
Messages
35
Dear all,

I have googled for a long time but cannot find out what's wrong.

Is it not possible to copy/paste when you use Range(Cell1,Cell2).copy / Range(Cell1,Cell2).paste

This is my code and it doesn't work. Of course WS, lastrowgp and WS3 are dim-ed

WS.Range(Cells(1, 1), Cells(lastrowgp, 1)).Copy WS3.Range(Cells(1, 1)).Paste



Thanks!!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Code:
[COLOR=#ff0000]WS.Range(Cells(1, 1), Cells(lastrowgp, 1)).Copy WS3.Cells(1, 1)[/COLOR]
without paste
 
Upvote 0
thanks, but if I have the WS3.Cells(1,1) on a line below, it still gives an error on the first part:

WS.Range(Cells(1, 1), Cells(lastrowgp, 1)).Copy


 
Upvote 0
Either:
Code:
Ws.Range(Cells(1,1),.Cells(lastrowgp,1)).Copy WS3.Cells(1,1)

Or:
Code:
Ws.Range(Cells(1,1),.Cells(lastrowgp,1)).Copy
Ws3.Cells(1,1).Paste
 
Upvote 0
The weird thing which I don't understand:

This code does not work:
WS.Range(Cells(1, 1), Cells(lastrowgp, 1)).Copy
WS3.Cells(1, 1).Paste

This code does work:
WS.Range(Cells(1, 1), Cells(lastrowgp, 1)).Copy
WS3.Range("A1").PasteSpecial xlPasteAll

Why is that? Why can't I refer to a cell without range?
 
Upvote 0
That's was a vexing issue for me when I was a VBA beginner.
It's a year old question but I'll solve it for those who might get here looking for a solution.

The correct form should be (my addition in red):
WS.Range(WS.Cells(1, 1), WS.Cells(lastrowgp, 1)).Copy
WS3.Range(
WS.Cells(1, 1)).Paste

The reason for this is that VBA is trying to be friendly and allows you to refer to active elements without the full qualifier. The problem occurs when you move the focus to another sheet or workbook.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top