|VBA has various modules that can contain code, and as we've seen, macros can act differently depending on where they are placed.|
Typically a macro of this type, which is accessing all sheets in the workbook, would be placed in what is typically called a "regular" or "standard" module. Some macros must be placed in a Sheet module and some must be placed in the ThisWorkbook module in order to function correctly.
To add a regular module to your workbook, open the VBA editor, click on Insert...Module and a Module1 window should open.
Another option is to create a personal.xls file and put your macros in modules in that workbook.
If you store a file called personal.xls in the XLStart directory (e.g. C:\Program Files\Microsoft Office\Office\XLStart) Excel will open that file every time you start the application and all macros stored in that file will be available for use in any workbook.
If within personal.xls you choose Window...Hide and then save it in XLStart, it will open "hidden" and you won't see it, but the macros will be available.