Method range of object worksheet failed

Question

I keep getting this error an searched for a solution, but couldn't find it. Can anyone point out what is wrong with below code?

    Sub test()
    Dim WKB As ThisWorkbook
    Set WKB = ActiveWorkbook
    Dim DataPivotSheet As Worksheet
    Set DataPivotSheet = WKB.Sheets("DataPivot")
    Dim YTDDataTableSheet As Worksheet
    Set YTDDataTableSheet = WKB.Sheets("YTD Data Table")
    Dim Startrow As Long

    If YTDDataTableSheet.Cells(2, 1) = False Then
    Startrow = YTDDataTableSheet.Cells(2, 1).Row
    Else: Startrow = YTDDataTableSheet.Cells(1, 1).End(xlDown).Offset(1, 0).Row
    End If

    YTDDataTableSheet.Cells(2, 22) = Startrow

    'Below line gets the error:
    DataPivotSheet.Range("J3", Range("A3").End(xlDown)).Copy 
    Destination:=YTDDataTableSheet.Range("A" & Startrow)

    End Sub

Do you have a line continuation on that line? The `Destination` is part of the same line. — Darren Bartrup-Cook, Nov 06 '18 at 10:07
Possible duplicate of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Luuklag, Nov 06 '18 at 11:05

score 0 · Answer 1 · answered Nov 06 '18 at 10:17

Common problem in Excel-VBA: You haven't qualified the Range("A3"). Although it seems obvious you want to access a range from DataPivotSheet, Excel doesn't do so if you don't tell. Instead, it will use a Range from the active workbook which is probably not DataPivotSheet.

You can write

DataPivotSheet.Range("J3", DataPivotSheet.Range("A3").End(xlDown)).Copy _
    Destination:=YTDDataTableSheet.Range("A" & Startrow)

or, maybe better to read (note the dot before the Range)

With DataPivotSheet
    .Range("J3", .Range("A3").End(xlDown)).Copy _
        Destination:=YTDDataTableSheet.Range("A" & Startrow)
End With

or, to split your rather complicated copy-statement (makes is easier to debug)

With DataPivotSheet
    Dim endCell As Range
    Set endCell = .Range("A3").End(xlDown)
    .Range("J3", endCell).Copy Destination:=YTDDataTableSheet.Range("A" & Startrow)
End With

As a rule of thumb: Never access Range without a dot. Even if you want to access a range from ActiveSheet (and you will not want this very often), it's better to write ActiveSheet.Range

Thanks for the answer. really helps allot also for writing future code. — Ryan, Nov 06 '18 at 10:25

score 0 · Answer 2 · answered Nov 06 '18 at 10:20

Range("A3").End(xlDown) will return a reference to a cell itself and not the address property of the cell.
As you do not specify the sheet with that range it will also look at the ActiveSheet. If that isn't the DataPivot sheet then it will be trying to create a reference from cells on different sheets.

You could use:

DataPivotSheet.Range("J3", Range("A3").End(xlDown).Address).Copy _
        Destination:=YTDDataTableSheet.Range("A" & Startrow)

Or

With DataPivotSheet
    .Range(.Range("J3"), .Range("A3").End(xlDown)).Copy _
        Destination:=YTDDataTableSheet.Range("A" & Startrow)
End With

On a side note working from the bottom up is often a better way to find the last row:

YTDDataTableSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Method range of object worksheet failed

2 Answers2