VBA Excel - cannot copy Range(Cells(),Cells()).copy??

[jmpmolegraaf]

Hello all,

Thanks for helping me out with this one.

++
Dim wsCS As Worksheet
Dim wsCOMBI As Worksheet
Dim lastrowcs As Integer

Set wsCS = ThisWorkbook.Sheets("RAW-CS")
Set wsCOMBI = ThisWorkbook.Sheets("RAW-Combi")

lastrowcs = wsCS.Cells(Rows.Count, 1).End(xlUp).Row

wsCS.Range(Cells(2, 1), Cells(lastrowcs, 1)).Copy Destination:=wsCOMBI.Cells(2, 1)


++
I get a runtime error here (1004) on the last line but I have no idea why. It works with Range.Copy but I want a range that is set by using Cells().

Is there a difference in Copy from a normal range vs. Copy from a Cells() range?


I am facing this problem in a lot of situations and I want to avoid activating sheets manually (sheets.activate).

Thanks, best regards,
Jan-Martin

 

[Jonmo1]

When using this syntax
Range(Cells(),Cells())

And you specify a sheet on the Range
wsCS.Range(Cells(),Cells())

Then you must also specify the same sheet on both Cells
wsCS.Range(wsCS.Cells(),wsCS.Cells())

 

[JoeMo]

Your post separates the Copy and Destination parts into two lines. If that's the way it is in your code then try this:

wsCS.Range(Cells(2, 1), Cells(lastrowcs, 1)).Copy Destination:=wsCOMBI.Cells(2, 1)

All on one line.

 

[jmpmolegraaf]

Hello Jonmo,

Normally I never apply the ws* before Cells and it's never a problem. Only when copying there is an issue.

What is the reason that you have specify the location of the cell when you do the already in the beginning?

 

[jmpmolegraaf]

Thanks JoeMo, all is in one line. So that isn't the problem!

it works when i activate the worksheet the line before and when i apply .PasteSpecial after putting the destination on a separate line:

wsCOMBI.cells(2,1).PasteSpecial xlPasteValues

I don't understand why i need to both activation and PasteSpecial to make it work. Only when i use .Range without Cells it works in one line.

 

[Jonmo1]

Cells is NOT part of Range.

The 2 Cells parts are executed first, the results of the Cells are then 'Passed' To Range.

A rule about how Ranges and Cells reference sheets is..
The ActiveSheet is assumed, unless otherwise specified.
If the code is held in a Worksheet Module, then Instead of the ActiveSheet, it's the sheet that holds the code.

So this
Range(Cells(),Cells())
Both the Range and Cells each refer to whatever sheet is active. Because it has not been otherwise specified.

With This
wsCS.Range(Cells(),Cells())
The Cells Still refer to whatever sheet is active because it's not otherwise specified on the Cells..
But the Range refers to wsCS because it WAS specified on the range.
If wsCS is NOT the currently active sheet, then it errors, because the Cells and Range are not referring to the same sheet.

With this
wsCS.Range(wsCS.Cells(),wsCS.Cells())
Both the Cells and Range are reffering to the same sheet (wsCS)


Hope that helps.

 

[jmpmolegraaf]

Thanks, never knew that and never ran into problems except for when applying copy-paste from one worksheet to another.

Great!

 

[Jonmo1]

You're welcome.