VBA copy to different worksheet

[nsdcoelho]

Hello all,

I've been finding some difficulties in writing simple VBA macros constructed to copy/paste values between different worksheets.
Can someone explain me why the following code does not work if my active worksheet (the worksheet from which I am running the code) is different that sheet1 (I've simplfied at maximum my code so I could try to understand this issue)?
I've been getting the error "Application-defined or object-defined error" but can't figure out why.

Sub copy()

Dim row1 As Integer, row2 As Integer
row1 = 4
row2 = 5
Worksheets("Sheet1").Range(Cells(row1, 2), Cells(row2, 4)).copy

End Sub


Just another question: where is the syntax error in the expression:

Worksheets("Sheet1").Range("B"&row1&":D"&row2).copy

Shouldn't this be equivalent to the line defined above?
Any help would be very appreciated.

 

[Momentman]

Maybe just the spaces between ampersands

Worksheets("Sheet1").Range("B" & row1 & ":D" & row2).copy

 

[nsdcoelho]

Thank you for your reply.
It turns out that the spaces where the problem, now this is no longer an issue (I thought that VBA automatically introduced the spaces where need). Thank you!
Any hints on the first problem?

 

[Momentman]

I really dont see the first problem
But to copy from one worksheet to another, you could do something like

Worksheets("Sheet1").Range("B" & row1 & ":D" & row2).copy worksheets("Sheet2").range("B1")