jueves, 17 de noviembre de 2011

Crea una hoja Index en tu Excel

Descargar el fichero: index.xlsm

Hagamos un indice en la primera hoja de un libro de Excel, para listar todas las hoja y establecer un hipervínculo que nos lleve a cada hoja. Cuando en un libro de Excel tenemos muchísimas hojas, en ocasiones es aburrido llegar a ir hasta una concreta. Para facilitar nuestro trabajo vamos a establecer una primera hoja llamada Index en la que vamos a escribir el nombre que queremos dar a cada hoja y luego las vamos a hipervincular.


Inicialmente las hojas del libro se denominan: Hoja1, Hoj2, y así hasta Hoja101.



Disponemos de una macro que renombra las hojas dándolas el nombre que hemos indicado en la columna C, comenzando en la celda C5. La primera hoja será la propia hoja en la que se encuentra el listado y que denominaremos Index. Las 100 hojas restantes irán tomando el nombre que se indica en la lista.


Código:

Sub renombra_hoja()
Dim Hoja As Worksheet
Dim Fila As Long
Fila = 5
For Each Hoja In Worksheets
  Hoja.Name = Cells(Fila, 3)
  Fila = Fila + 1
Next
End Sub



Disponemos de otra macro que crea los hipervínculos.


Código:

Sub Hipervincula()
Dim Nombre As String
Range("C5").Select
Do
  Nombre = Selection.Value
  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
      "'" & Nombre & "'!A1", TextToDisplay:=Nombre
  ActiveCell.Offset(1, 0).Select
Loop While Not IsEmpty(ActiveCell)
Range("A1").Select
End Sub


Incluimos dos botones que lanzan las macros.


Debemos tener especial cuidado de que en la lista existan tantos nombres como hojas existen el el libro de Excel en el que estamos.

¿Y si no quiero cambiar los nombres?

En caso de que desees establecer los hipervínculos pero no quieras cambiar los nombres de las hojas, puedes recurrir al ejemplo siguiente.


Disponemos de una macro que lista los nombres de las hojas existentes y luego le pasamos la macro que crea los hipervínculos.

La macro que lista las hojas existentes es la siguiente.

Código:

Sub Lista_nombres()
Dim Hoja As Worksheet
Dim Fila As Long
Fila = 5
For Each Hoja In Worksheets
  Cells(Fila, "G") = Hoja.Name
  Cells(Fila, "F") = Fila - 4
  Fila = Fila + 1
Next
End Sub

La macro que realiza los hipervínculos la hemos llamado Hipervincula2 que es similar a la macro Hipervincula. Lo único que cambia es la celda de inicio que ataca la macro.

9 comentarios:

  1. HOla, muy bueno el post, y aún mejor la pagina¡¡. Ahora bien, si ya ada hoja tiene nombre, y no necesito renombrar sino solo el hipervinculo a cada una dado que son muchas que puedo hacer?
    De antemano Gracias¡¡

    ResponderEliminar
  2. Hola Mari Ox: Este código te permitirá lograr lo que propones. Saludos. "eventoclic@gmail.com"

    Sub CrearIndiceDelLibro()
    Pregunta = MsgBox("Desea crear el índice en una hoja nueva?", _
    vbExclamation + vbYesNoCancel, "Donde crear el índie...")

    If Pregunta = vbCancel Then Exit Sub
    If Pregunta = vbYes Then Worksheets.Add Before:=Worksheets(1)

    With [B2]
    .Value = "Contenido de este libro": .Font.Size = 12
    .Font.Bold = True: .Font.Underline = xlUnderlineStyleSingle
    End With

    For Each HOJA In Worksheets
    If HOJA.Name <> ActiveSheet.Name Then
    INCREMENTO = INCREMENTO + 1
    ActiveSheet.Hyperlinks.Add _
    Anchor:=ActiveSheet.Cells(3 + INCREMENTO, 3), _
    TextToDisplay:=HOJA.Name, _
    SubAddress:=HOJA.Name & "!A1", _
    Address:=""
    End If
    Next
    End Sub

    ResponderEliminar
  3. Hola Mary Ox.

    He añadido al Post el apartado:

    "¿Y si no quiero cambiar los nombres?"

    Espero que te sirva.

    Un cordial saludo.

    ResponderEliminar
  4. Hola Adolfo, un placer saludarlo:
    Tengo un libro con 38 hojas y en cada hoja tengo 38 hipervínculo, en las celdas D6:D24, con el nombre 1ºJornada, 2ºJornada así sucesivamente hasta 19ºJornada y en la misma hoja en la celdas E6:E24, con el nombre 20ºJornada, 21ºJornada así sucesivamente hasta la 38ºJornada, necesito renombrar sino solo el hipervíncularla a cada una de las hojas, ya que son 1444 hipervínculos me resulta muy tedioso.
    De antemano Gracias!!!
    Y muchas Felicidades por tu Blog.

    ResponderEliminar
  5. Muchas gracias por los todos los consejos que nos das.
    Saludos cordiales

    ResponderEliminar
  6. Hola Excelente tu aporte, pero en mi caso, siempre me peleo con las pestañas del archivo en excel, porque los estados financieros que reviso tienen mas de 60 pestañas y me gustaria saber si es posible aplicar este indice en la barra de tareas como una lista desplegable para ir navegando entre las hojas,alguna idea???

    ResponderEliminar
    Respuestas
    1. Hola Martel.
      Una posible solución sería crear una macro en el libro PERSONAL.XLSB y un icono que la pueda lanzar. La macro lo que haría es crear la hoja INDEX y en ella se recogería un listado de todas las hojas que tenga tu libro de Excel. Ese listado se podría hipervincular y actualizar en todo momento con otra macro.
      Quedaría estupendo.

      Eliminar
  7. Gracias, he experimentadotu codigo con un generador de kardex escolar y funciona muy bien

    Gracias de antemano

    ResponderEliminar
  8. Necesito que el nombre del archivo lo asigne lo que escriba en una celda.
    Gracias

    ResponderEliminar