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

[jmpmolegraaf]

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!!

 

[Jonmo1]

Paste is not a member/propety of Range/Cells
PasteSpecial IS.

Paste is a member/property of a SHEET, and pastes to the ActiveCell of that sheet.

That's why we do either pastespecial OR the 1 line command of
Range("A1").Copy Destination:=Range("B1")
That is commonly done without writing Destination, just
Range("A1").Copy Range("B1")

 

[olarra91]

Dear everyone,

I´m having trouble when copying a range using: .Range(Cells(),Cells())

A. This works:
Wb1.Worksheets("Gafetes").Range("D4:AD6").Copy

B. This doesn´t work
Wb1.Worksheets("Gafetes").Range(Cells(4, 4), Cells(6, 30)).Copy

C. This doesn´t work
Wb1.Gafe.Range(Wb1.Gafe.Cells(4, 4), Wb1.Gafe.Cells(6, 30)).Copy


I need B or C to work because that way I can assign a variable value to the Cells coordenates.


Thank you

 

[Jonmo1]

Try
Wb1.Worksheets("Gafetes").Range(Wb1.Worksheets("Gafetes").Cells(4, 4), Wb1.Worksheets("Gafetes").Cells(6, 30)).Copy

Or you can simplify like this

With Wb1.Worksheets("Gafetes")
    .Range(.Cells(4,4), .Cells(6,30)).Copy
End With

 

[olarra91]

Thank you @Jonmo1 it finally worked...however I have a question: previously I did the same in another file and I did not wrote the "dot . " before the word Cells and it also worked...why do i need to write down ".Cells" in one file and i the other is just fine with "Cells"

Cheers

 

[Jonmo1]

There are several factors to consider.

Where is the code residing in your book? In a Standard Module (from clicking Insert - Module), or in a built in Worksheet module?
Which sheet is currently active at the time the code runs ?

Code held in a Standard module, range (and cells) references will refer to the currently active sheet, unless otherwise specified.
Range("A1") - refers to the currently active sheet
Unless otherwise specified
Sheets("Sheet3").Range("A1") - refers to sheet3 regardless of which sheet is currently active.

Code held in an Object module like a Worksheet Module, range and cells references will refer to THAT object unless otherwise specified, regardless of the currently active sheet.
So in say Sheet3's module
Range("A1") - refers to Sheet3 regardless of currently active sheet
Unless otherwise specified
Sheets("Sheet1").Range("A1") - refers to Sheet1 regardless of currently active sheet and the module holding the code.


When you do this
Sheets("Sheet1").Range(Cells(), Cells())
You've specified the sheet on the RANGE, but have not specified it on the Cells, therfor allowing Cells to refer to whatever it defaults to depending on above rules.
If the Cells functions are referring to a sheet that is different from the sheet referenced by Range, then you get that error.


It is a 'Best Practice' to always qualify the sheet on all range and cells references.
Just so you don't have to worry about which sheet is currently active while your code is executing.

If it has worked for you in the past without specifying both, it is merely coincidental that both the Range and Cells functions were referring to the same sheet.

Hope that helps.

 

[olarra91]

Awesome man, great help @Jonmo1!