sábado, febrero 23, 2008

Crear un índice de hojas en un cuaderno de Excel.

Una de las consultas que recibo con cierta frecuencia es cómo crear un índice de las hojas de un cuaderno Excel.

Cuando queremos navegar de una a otra hoja del cuaderno podemos usar varios métodos, por ejemplo usar las flechas de navegación en la esquina inferior izquierda de la hoja o hacer un clic con el botón derecho del mouse sobre ellas para abrir el menú de navegación

indice de hojas

Pero cuando tenemos muchas hojas en un cuaderno, por ejemplo una por semana del año, estos métodos resultan incómodos.
La solución es crear una hoja que haga de índice. Las entradas del índice son los nombres de las hojas, a las cuales le hemos incorporado un enlace (hyperlink), de manera que con un solo clic podamos navegar a la hoja deseada.

Cuando se trata de pocas hojas, podemos hacer esto en forma manual. Pero cuando tenemos un cuaderno con un gran número de hojas, lo mejor es utilizar una macro.
Esta macro, que pueden descargar aquí, nos ofrece la opción de crear una nueva hoja que contenga el índice


indice de hojas

Si aceptamos la opción, la macro crea la nueva hoja, en la celda A1 pone el título "Índice" y a partir de la fila 3, pondrá los nombres de las hojas con un enlace a la celda A1 de cada una de ellas.

indice de hojas

Si no aceptamos la opción de crear una nueva hoja, el índice es creado a partir de la celda activa del cuaderno.




Technorati Tags:

23 comentarios:

  1. Como podria obtener el codigo, ya que el vinculo esta denegado.
    Desde ya muchas gracias

    ResponderBorrar
  2. Hola Javier

    acabode porbar el´enlace y pude descargar el archivo sin ningún problema.
    Si no me mandas tu dirección electrónica, te lo puedo mandar por mail.

    ResponderBorrar
  3. En primer lugar agradecer sinceramente todas sus aportaciones ya que todas son prácticas, automatizando y simplificando tareas. Mi pregunta es que al utilizar esta macro se crea correctamente la hoja indice pero al picar en el vinculo para las páginas me dice vínculo no válido en algunas, no en todas. Por que puede ser? Gracias.

    ResponderBorrar
  4. Mándame el archivo para que pueda ver cual es el problema.

    ResponderBorrar
  5. Jorge felicitarte por tu pagina que realemnte es una gran ayuda para las personas que nos gusta y que ademas utilizamos el excel.ç
    Lamentablemente no puedo abrir la macro desde el blog, por favor si no es mucha molestia podrias enviarlo a mi correo calin33@gmail.com
    gracias.

    ResponderBorrar
  6. Hola
    si no puedes abrir la macro, lo más probable es que las definiciones de seguridad lo impidan.
    En el menú Herramientas--Macro, abre el diálogo de Seguridad y ponlo a "medio" o "bajo".
    Si has copiado el código del archivo eliminar la primera línea, que es sólo un encabezamiento.

    ResponderBorrar
  7. Jorge, tu blog me parece fabuloso y nos resultó de gran ayuda en mi trabajo, te cuento que quise aplicar el macro pero me surgió erro de sintaxis!!. puede que este aplicando mal el macro, te cuento: abri el archivo, luego fui a macros y pegue el macro, me podrías guiar como correrlo ? desde ya muchas gracias!!

    ResponderBorrar
  8. Hola Diego

    cuando copias el código, no tienes que copiar la primer línea:

    Attribute VB_Name = "Crear_Indice"

    ResponderBorrar
  9. Jorge, felicitaciones por tu Blog, estoy ocupanto tu macro y es excelente, te cuento mi situacion: tengo un libro que descarge de una aplicacion, aveces puede tener 10 hojas como 100, lo que hago es mostrar en pantalla el n° de hojas que hay, luego creo un libro nuevo(con el codigo de tu macro)para crear todos los vinculos a los libros pero estos no funcionan ya que los nombres de los libros tienen espacios¿como podria solucionar esto?si me puedes ayudar te lo agredeceria mi correo es tomuer01@gmail.com...gracias

    ResponderBorrar
  10. No me parece que los espacios en los nombres de las hojas sean el problema. Es decir, supongo que te refieres a un cuaderno con 10 o 100 hojas y no a 100 cuadernos.

    ResponderBorrar
  11. Exacto, me refiero a las hojas no al libro, lo redacte mal...los nombres de las hojas tienen este formato: miercoles, 25 de junio del 2008, cuando junto el nombre(ejemplo: 25062008) me vincula bien pero con el nombre completo me dice que hay un error en la referencia??...otra cosa,como podria mostrar los nombres hacia el lado y no hacia abajo?y si es problema del nombre como se lo podria cambiar??. Gracias.

    ResponderBorrar
  12. Hola

    efectivamente, surge un problema ya que el string que usa el Hyperlink incluye una comilla al principio y al final del nombre de la hoja.
    Tienes que modificar parte del código.
    En el Sub insertar_indice
    donde dice

    SubAddress:= wksHoja.Name & "!A1"

    debe decir

    SubAddress:="'" & wksHoja.Name & "'" & "!A1"

    ResponderBorrar
  13. El archivo tiene extension .bas, como la uso?

    ResponderBorrar
  14. El archivo con la extensión .bas es un archvio de texto con el código de la macro.
    Puedes importar el código a un módulo del editor de Visual Basic o copiar el código en el módulo.

    ResponderBorrar
  15. hola jorge ejecuto la macro y arma el indice sin ningun problema pero cuando dou click en el vinculo me arroja un mensaje de referencia no valida

    ResponderBorrar
  16. jorge , lo hago en un libro nuevo y si funciona!!!!

    ResponderBorrar
  17. Hola
    ¿a que te refieres con "cuadernos nuevos"? He probado la macro con cuadernos previamente guardados y cuadernos recién creados y sin guardas y veo que funciona en a,bos casos.

    ResponderBorrar
  18. Excelente utilidad, muchas gracias por compartirlo

    ResponderBorrar
  19. Ya resolví lo de la referencia invalida, lo que pasa es que no había notado que entre las " " había una comilla simple o apostrofe ', ese era el error, Gracias de antemano
    SAludos
    Alexx
    Los Mochis, Sinaloa, México
    Vaya que hay que sudar para aprender GRACIAS

    ResponderBorrar
  20. Hola Jorge,

    Excelente aportación pero me surge una duda
    Si ocultas las páginas enlazadas, los hipervinculos dejan de funcionar , ya que las hojas están ocultas...

    Tiene solución?

    ResponderBorrar
  21. En realidad los hipervínculos siguen funcionando, pero la hoja oculta no puede ser vista.
    La solución es usar código (macro) para volver visible la hoja. Pero, ¿cuál sería el objetivo de ocultarlas en ese caso?

    ResponderBorrar
  22. Hola Jorge, bueno continúo tratando de vincular las hojas de un programita que tengo hecho en excel para diseñar sesiones de entrenamiento, éste sistema es bueno pero unidireccional, es decir no me permite regresar desde la hoja a la cual me ha redireccionado a la anterior, o navegar entre ellas, ya que la hoja índice queda oculta, este código se podría insertar en un botón, o módulo donde se vea el texto desplegado y al clicar sobre el mismo se pueda ir a la hoja deseada, ya que con insertar un botón por hoja se solucionaría la navegación. Por supuesto agradecido por tus respuestas.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.