We have a complex macro enabled spreadsheet. Today we got a ticket from the business user that Excel is throwing error “Method ‘Sheets’ of object ‘_Global’ failed.”. I searched online but could not find the straight forward answer. None of the online discussion was giving the root cause of the error and everyone was discussing how to change the code to fix the error. The spreadsheet which was working perfectly fine for couple of years and suddenly stopped working, it wasn’t the code but it was something else and I needed the root cause of this error.
Finally found the root cause of this error at http://softwaresalariman.blogspot.com/2010/05/excel-inplace-or-embedded-without.html. Author discusses that Sheets is shortcut of Application.ActiveWorkbook.Sheets so if for whatsoever reason, the Application.ActiveWorkbook becomes null due to the lost focus, this error would be thrown.
Then I commented out the code which was causing an issue and found that even though the worksheet was open, I could not see it. Searching on Internet, I found that this could be caused if the workbook is hidden. Once I unhide the workbook from View > Window > Unhide, everything worked as expected.