0

When trying to reference a range on another worksheet.

I get

runtime error 1004 Application defined or object defined error.

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

Option Explicit

Sub rangecopy()

    'This works
    ThisWorkbook.Worksheets("Sheet2").Range("A1:C1").Copy 

    'This only works if sheet 2 is active
    ThisWorkbook.Worksheets("Sheet2").Range(Cells(1, 1), Cells(1, 3)).Copy

End Sub

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
enter image description here

These fields did not show errors yesterday, and I was able to reference cells from other sheets.

Community
  • 1
  • 1
Francois Roumel
  • 3
  • 2

1 Answers1

0

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

Terio
  • 507
  • 2
  • 5
  • This guy (me) has been in a code hole for a couple days. I need to take more breaks. Your answer got me to review my references in the monster workbook I was in and all seems to be working now. Thank you so much! – Francois Roumel Sep 15 '22 at 08:08
  • I'm glad I was helpful, thanks for the feedback. – Terio Sep 15 '22 at 08:19