How to resolve VBA Error "Unable to get the selected property of the range class "

Question

I am new to VBA and just started learning on my own. When I try to run my program...

Sub cpy()
    range("k1:k12").Select
    Selection.copy
    Application.Goto Worksheets("Sheet1").range("m4").Select
    ActiveSheet.Paste
End Sub

... I get the following error: Unable to get the selected property of the range class.
So I tried to run the erroring line separately:

Sub cck()
    Application.Goto Worksheets("Sheet1").range("m4").Value
End Sub

It shows the following error: Run-time error '1004': Reference isn't valid.

The Excel file is new, contains 3 sheets and the sheet names are not altered. I tried to run the above codes on Sheet3.

Answer 1

Application.Goto itself already selects, so tacking on Select at the end like that doesn't make sense. Application.Goto performs an action -- it goes to a certain range, it doesn't return a range which you need to select.

In any event, the best way to resolve the error is to eliminate Select from your code. It is seldom needed. See How to avoid using Select in Excel VBA.

Even though that linked question is explicitly about Select, it applies to Application.Goto as well. Both Select and Goto lead to spaghetti code. A classic essay that all programmers should read at least once is Go To Statement Considered Harmful by Dijkstra. He didn't have Application.Goto in mind of course, but it nevertheless applies.

Answer 2

Three Ways of Copying a Range

• Range.Copy method, Range.PasteSpecial method, Application.CutCopyMode property
• At this stage, step-by-step is the way to go: use Option Explicit, declare variables (e.g. Dim wb As Workbook), and create references to (Set) all objects (e.g. Set ws = ThisWorkbook).

Option Explicit

Sub copyRangeCopy()
    
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet3")
    Dim srg As Range: Set srg = sws.Range("K1:K12")
    
    ' Destination
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet1")
    Dim dCell As Range: Set dCell = dws.Range("M4")
    
    ' Copy (values, formulas, formats)
    srg.Copy dCell
    
    ' Copy by assignment (values only, but very fast)
    'dCell.Resize(srg.Rows.Count) = srg.Value
    
    ' Copy/PasteSpecial (many options, but the destination sheet becomes active)
    'srg.Copy
    'dCell.PasteSpecial ' many options
    'Application.CutCopyMode = False
    
End Sub