.copy _ Destination syntax, Range Error

Question

Hello I am attempting to use the .copy_ destination syntax as opposed to copy and paste in a VBA loops and I am getting a "Range of Object'_Worksheet Failed error

Currently my code looks like this

For x = 3 To LastHBR
If InStr(1, HBWS.Cells(x, Tickercolumn - 1), "Total") = 0 Then
    HBWS.Range(Cells(x, 1), Cells(x, ClastHBC)).Copy _
    Destination:=MWS.Range(Cells(LastMWSR + x - 2, 3), Cells(LastMWSR + x - 2, CLastMWSC))
End If
Next

Any idea why that range is throwing an error? I've tried a few variations but I can't get it to land.

For reference

HBWS & MWS are defined worksheets

ClastHBC, LastMWSR etc... are defined last rows/columns in the worksheet.

SJR SJR · Accepted Answer · 2018-05-31T12:41:01

Probably because you need to qualify all your cells references with a sheet name as otherwise the active sheet will be assumed.

For x = 3 To LastHBR
    If InStr(1, HBWS.Cells(x, Tickercolumn - 1), "Total") = 0 Then
        HBWS.Range(HBWS.Cells(x, 1), HBWS.Cells(x, ClastHBC)).Copy _
                Destination:=MWS.Range(MWS.Cells(LastMWSR + x - 2, 3), MWS.Cells(LastMWSR + x - 2, CLastMWSC))
    End If
Next

.copy _ Destination syntax, Range Error

1 Answers