Hola, bueno es solo el ejemplo para que te des una idea de como lo puedes hacer. Dime si tienes conocimientos de programación o cero? Esto es para no cometer el error de asumir que entiendes ciertas cosas que para mí son obvias (por que soy programadora) y tal vez tú no tengas idea y no solo por el hecho de ser "nuevo en el uso de las macros".
Puedes sustituir en lugar del nombre de la hoja el número por lo tanto el ciclo recorrería todas las hojas por numero así es mas fácil y ya puede ser variable. Si no, se puede programar tmb que al abrir el libro se recorran todas las hojas tomando el nombre de cada una y almacenandolo en un arreglo, o incluso ecribiéndolas en una hoja al final del libro. el cielo es el límite... vaya hay miles de soluciones solo hay que ser creativos.
El activecell se puede resolver de dos maneras: te cambias de hoja desde la macro con el ciclo y activas desde la macro una celda, la que quieras. O bien no incluyes el parametro "AFTER" y con esto la busqueda se inicia en la primera celda del lado izquierdo.
te dejo este link para el manejo de las hojas, para que sepas las distintas formas de referenciarlas :) por aquí seguiré:
Manejo de Hojas y libros en excel