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 could not find an answer even after searching online for hours. None of the online discussion was giving the root cause of the error. Everyone were discussing how to change the code to fix the error. The spreadsheet was working fine for couple of years but it suddenly stopped working. I suspected that it wasn’t the code but it was something else. I needed the root cause of this error.
Finally, I found the root cause of this error at this link. Author discusses that Sheets is shortcut of Application.ActiveWorkbook.Sheets. For whatsoever reason, if the current sheet looses focus, the Application.ActiveWorkbook becomes null and code throws this error.
I temporarily commented out the code which was causing an issue. Worksheet was not visible, even though the it was open. Somehow the workbook was hidden. Everything worked as expected once I unhide the workbook from View > Window > Unhide.