2

I wonder why the following line gave

"Application-defined or Object-defined error"

If Sheet6 is selected, this line runs fine - no error message. However, if another sheet is selected, Excel throws the above error message.

ThisWorkbook.Sheets("Sheet6").Range(Cells(1, 2), Cells(12, 1000).End(xlToLeft)).ClearContents
0m3r
  • 12,286
  • 15
  • 35
  • 71
joehua
  • 725
  • 3
  • 10
  • 25

1 Answers1

4

You need to qualify all your Range objects with your Worksheet.

With ThisWorkbook.Worksheets("Sheet6")
    .Range(.Cells(1, 2), .Cells(12, 1000).End(xlToLeft)).ClearContents
End With

You didn't qualify Cells() with your worksheet, so it pulls from the ActiveSheet instead of Worksheets("Sheet6").

So, remember that when your thinking "It only works when it's on that sheet" then this is going to be the issue.

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
  • Thanks. Now that you pointed it out, I remember I had a similar problem few years back. I had solved the problem by qualifying Cells but the solution escaped me this time. – joehua Nov 09 '18 at 00:25