VBA Worksheet Object Cells Properties error-CodePudding

When trying to reference a range on another worksheet, I've come across a runtime error 1004 Application defined or object defined error.

At first I was wondering if I was just doing something wrong, but on double checking my syntax and methods online and breaking everything down to it's simplest case, there seems to be something very wrong and I'm struggling to find a solution.

So here goes, I have a simple workbook with 2 sheets, for the sole purpose of figuring this out, and I am simply trying to copy a range from sheet 2 while sheet 1 is active.

Option Explicit

Sub rangecopy()
    ThisWorkbook.Worksheets("Sheet2").Range("A1:C1").Copy 'This works
    ThisWorkbook.Worksheets("Sheet2").Range(Cells(1, 1), Cells(1, 3)).Copy 'This only works if sheet 2 is active
End Sub

What's really seetting off red flags here is that many properties of the Worksheets object are showing the application or object defined error, including the value fields which would normally show an array

Worksheets Object Item 2 ("Sheet2") Cells Properties from Watch window

I'm going to put my head through a wall, because these fields did not show errors yesterday, and i was able to reference cells from other sheets without issue. Please help this poor soul!

CodePudding user response：

Try to add refer to worksheet in range definition:

Option Explicit

Sub rangecopy()
    With ThisWorkbook.Worksheets("Sheet2")
        .Range("A1:C1").Copy 'This works
        .Range(.Cells(1, 1), .Cells(1, 3)).Copy ' <-- Try this
    End With
End Sub

Bye