Resolved: Method ‘Sheets’ of object ‘_Global’ failed

Category: MS ExcelTags: ,

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.

Share

8 comments

Your email address will not be published. Required fields are marked *

  1. Julio says:

    Muchas gracias, llevaba muhcho tiempo batallando con este error

  2. Jorge Cerol says:

    You saved me. I Spent lots of time because de same code was working with other sheets. Now it’s OK. Thanks

  3. Rob Sands says:

    Thanks this saved me a lot of time

  4. Chilli says:

    You will see it more frequently now, since the same thing happens if you don’t have enabled editing, before you start the macro.

  5. simon says:

    Thanks for this. In my case it turned out the excel was opened in protected view inadvertently. I closed it – reopened it and it worked. Your solution told me straight away where it was coming from .

    Cheers

  6. mohamed says:

    thanks thanks thanks very much.

  7. Sandra says:

    Thank you so much!

  8. Joseph says:

    THANKS FOR SHARING