Esto es un despiste típico, entre el libro activo, el libro que contiene el código, otros libros abiertos, etc.

personalmente, cuando desde un complemento me quiero referir al propio complemento utilizo thisworkbook, y luego lo que sea, por ejemplo thisworkbook.sheets("hoja1")...

Más elegante es la utilización de variables de tipo Objeto, que simplifican la manera en que te refieres a una hoja en concreto, o a un libro

aunque para la mayoría de los casos, teniendo un poco de cuidado con el libro/hoja que está activo en cada momento, puedes resolverlo con activesheet, activecell, etc. 

------
Ya sé Excel, pero necesito más
La potencia sin control no sirve de nada.
agradecimientos