VBA copy to different worksheet

nsdcoelho

New Member
Joined
Jul 30, 2014
Messages
10
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
Maybe just the spaces between ampersands
Code:
Worksheets("Sheet1").Range("B" & row1 & ":D" & row2).copy
 

nsdcoelho

New Member
Joined
Jul 30, 2014
Messages
10
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

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
I really dont see the first problem :)
But to copy from one worksheet to another, you could do something like

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

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,312
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top