Debugging. Copy a variable number of cells in one sheet and paste into another sheet

Question

The objective of the code is to copy a n number of rows and three columns of cells from Sheet2 into the last empty row in Sheet1. I attempted using cell properties in the range to copy but this line is giving me a Runtime error '1004' (Application-defined or object-defined error).

How can this be rectified?

Private Sub CommandButton1_Click()
    Dim sum As Integer
    n = 7
    Sheets("Sheet2").Range(Cells(11, 15), Cells((11 + n), 18)).Copy
    With Sheets("Sheet1").Range("A500").End(xlUp).Offset(1, 0)
        .PasteSpecial xlPasteFormats
        .PasteSpecial xlPasteValues
    End With
End Sub

user1274820 user1274820 · Accepted Answer · 2015-06-02T18:37:38

One [issue] which often catches people out when passing range objects as arguments to the Range property is that if you need to specify a worksheet object (which is good practice), you need to specify it for all of the Range/Cells properties you use.

(Source/More Reading: http://excelmatters.com/referring-to-ranges-in-vba/)

You could use:

With Sheets("Sheet2")
    .Range(.Cells(11, 15), .Cells((11 + n), 18)).Copy
End With

Or:

Sheets("Sheet2").Range(Sheets("Sheet2").Cells(11, 15), Sheets("Sheet2").Cells((11 + n), 18)).Copy

Instead of:

Sheets("Sheet2").Range(Cells(11, 15), Cells((11 + n), 18)).Copy

Or you could build the range like this:

Sheets("Sheet2").Range("O11:R" & (11 + n)).Copy

Edited Code:

Private Sub CommandButton1_Click()
Dim sum As Integer
n = 7
Sheets("Sheet2").Range("O11:R" & (11 + n)).Copy 'Edited Line
With Sheets("Sheet1").Range("A500").End(xlUp).Offset(1, 0)
    .PasteSpecial xlPasteFormats
    .PasteSpecial xlPasteValues
End With
End Sub

Debugging. Copy a variable number of cells in one sheet and paste into another sheet

1 Answers