Crear un indice en un libro de excel

Podemos crear un indice de nuestras hojas , en un libro de excel .
Tenemos dos formas de crearlo.

1 ª Manualmente

Podemos tener en el  principio de nuestro libro una hoja donde colocaremos los hipervínculos al resto de las hojas.
Para crear los hipervínculos es muy sencillo ya que solo debemos hacer clic derecho sobre la celda indicada y seleccionar la opción hipervínculo.

Se mostrará el cuadro de diálogo Insertar hipervínculo y en el panel izquierdo deberás seleccionar la opción Lugar de este documento.

Este cuadro de diálogo nos permite crear un hipervínculo a una celda dentro de la misma hoja o a una de las hojas de nuestro libro que es precisamente la opción que utilizaremos en nuestro índice.

Después de haber hecho la selección adecuada debemos hacer clic en el botón Aceptar para crear el hipervínculo.

 De la misma manera podemos insertar un vínculo en cada una de las hojas “destino” para que nos lleve de regreso al índice de manera que se facilite la navegación entre todas las hojas.

1 ª Macro para crear un indice

ver código 

- Copia este código.

Sub crearIndice()
 
'**************************************
'PASO 1: Crear o limpiar la hoja Indice
'**************************************
Dim hoja As Worksheet
On Error Resume Next
Set hoja = Worksheets("Indice")
On Error GoTo 0
 
If hoja Is Nothing Then
    'La hoja Indice no existe - Crearla en primera posición
    Worksheets.Add(Before:=Worksheets(1)).Name = "Indice"
Else
    'La hoja Indice ya existe - Limpiarla
    Worksheets("Indice").Cells.Clear
End If
 
'Insertar título a la hoja Indice
Worksheets("Indice").Range("A1").Value = "Indice"
 
 
'************************************************
'PASO 2: Recorrer las hojas creando hipervinculos
'************************************************
Dim fila As Long
Dim vinculoRegreso As String
 
fila = 2
'Celda donde se colocará el hipervinculo de regreso al indice
vinculoRegreso = "C1"
 
For Each hoja In Worksheets
    If hoja.Name <> "Indice" Then
        'Crear hipervinculo en hoja Indice
        With Worksheets("Indice")
            .Hyperlinks.Add Anchor:=.Cells(fila, 1), _
            Address:="", _
            SubAddress:="'" & hoja.Name & "'!A1", _
            TextToDisplay:=hoja.Name
        End With
         
        'Crear hipervinculo en hoja destino hacia Indice
        With hoja
            .Hyperlinks.Add Anchor:=.Range(vinculoRegreso), _
            Address:="", _
            SubAddress:="Indice!A1", _
            TextToDisplay:="Indice"
        End With
        fila = fila + 1
    End If
Next
 
End Sub

El paso 1 de la subrutina es para validar la existencia de la hoja llamada Indice. En caso de que no exista será creada con la instrucción Worksheets.Add pero si ya existe se limpiará el contenido de todas sus celdas con el método Cells.Clear.

La parte más importante del paso 2 es el bucle que recorre todas las hojas del libro y para cada una de ellas inserta un hipervínculo en la hoja Indice.
También se crea otro hipervínculo dentro de cada hoja individual que nos permitirá regresar a la hoja Indice.
Este vínculo se insertará en la celda indicada por la variable vinculoRegreso, que para nuestro ejemplo es la celda C1.
Para nuestro libro de Excel de ejemplo, nuestra macro crea una hoja llamada Indice con los siguientes vínculos:

Crear una hoja indice en Excel

Además para cada una de las hojas del libro, se coloca el hipervínculo hacia la hoja Indice en la celda C1 de la siguiente manera:

Crear índice con links a las demás hojas en Excel

Para crear el índice de hojas en tu propio libro deberás copiar la macro al módulo ThisWorkbook y ejecutarla.

Recuerda que para ejecutar la macro solo debes ir a Vista > Macros y dentro del cuadro de diálogo seleccionar la macro ThisWorkbook.crearIndice.