Run-time error '1004' when using fully qualified cells in range object

Question

I am trying to copy values from one workbook and paste them into another using the cells property of the range object. How do I properly state range references so that I am not receiving a '1004' runtime error?

I'm working on Excel 2013, and I am running the code in "Practicebook" workbook with an active worksheet.

I've researched many similar problems like this one, Run time error 1004 in Range(Cells()), but they have not helped me. I've made sure my references are fully qualified.

Sub Transfer()

    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet

    Set wsCopy = Workbooks("Practicebook2").Worksheets("Sheet1")
    Set wsDest = Workbooks("Practicebook").ActiveSheet
    wsCopy.Range(wsCopy.Cells(2,8)).Copy
    wsDest.Range("J5").PasteSpecial

End Sub

When I run the code, error message "Method 'Range' of object'_Worksheet' failed" appears, with the 6th line of code pasted below highlighted by the debugger. When changing the reference to A1 style notation, the code produces the pasted value in the destination sheet as expected.

Answer 1

Range expects a string or TWO cells to define the range.

With only one CELL remove the RANGE:

wsCopy.Cells(2,8).Copy

When using two cells it would be something like this:

wsCopy.Range(wsCopy.Cells(2,8),wsCopy.Cells(4,10)).Copy

If instead you have the range desired as a string in the the cell one would need to append .Value to the Cells()

wsCopy.Range(wsCopy.Cells(2,8).Value).Copy

Now it will take the value in that cell as a string an pass it to the Range.

Also skip the dual lines:

wsCopy.Cells(2,8).Copy wsDest.Range("J5")

Copy takes an argument of the destination.

Answer 2

Two things you can consider when working with ranges:

1- You can refer to sheets by their code names and this prevents an error if someone changes their name in Excel

2- You don't need to use the cells inside a range. So code could look like this:

Sub Transfer()

    'Dim wsCopy As Worksheet -> Don't need this if you refer to the sheet with it's codename
    Dim wsDest As Worksheet

    'Set wsCopy = ThisWorkbook.Worksheets("Sheet1")  -> Don't need this if you refer to the sheet with it's codename
    Set wsDest = ThisWorkbook.ActiveSheet
    Sheet1.Cells(2, 8).Copy wsDest.Range("J5") ' -> Refer directly to source sheet codename

End Sub