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
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.