Crear hoja de índice automáticamente

Esta es una rutina en VBA para crear una hoja de indice automáticamente para tu libro. Esto facilitará el desplazamiento por las distintas hojas del libro, especialmente cuando el número de ellas sea grande y no todas las pestañas queden a la vista.

Con este código podrás agregar una hoja al inicio del libro llamada Indice donde se creará un listado con el nombre de todas las hojas. Cada uno de estos nombres, tendrá un hipervínvulo para posicionarse en la hoja seleccionada.

En cada hoja, además, se añadirá un enlace que nos permitirá volver al índice con solo pulsarlo. Este enlace se insertará en la celda «A1». La rutina, antes de crear el índice, preguntará si se debe insertar una fila nueva en cabecera, para que, en el caso de que ya haya datos en la fila 1, se desplacen hacia abajo y no los borremos.

Nuestro objetivo será crear una hoja Indice como esta:

Y, en cada hoja, un enlace en la celda A1 para retornar al índice.

Paso 1. Declaración de variables

Como siempre, comenzaremos por declarar las variables que vamos a utilizar. Esto es una buena práctica que nos evitará en muchas ocasiones problemas de depuración. Si nos equivocamos al escribir el nombre de una variable y, por error, en algún punto de nuestro código tiene un carácter diferente, te aseguro que encontrar ese error es muchas veces una tarea ardua. Utilizando la opción Option explicit, VBA nos obliga a declarar todas las variables y no podemos usar una no declarada. De esta forma, un error al teclear, será detectado automáticamente.

Option Explicit

Sub CrearIndice()
     Dim HojaIndice As Worksheet
     Dim Hoja As Worksheet
     Dim Fila As Integer
     Dim InsertarFila As Integer
     Dim Mensaje As String

Paso 2. Comprobar si ya existe Indice

El siguiente paso será comprobar que no exista ninguna hoja con el nombre Indice pues de ser así, nos generaría un error en tiempo de ejecución, al no poder nombrar una hoja nueva con un nombre ya existente.

Para ello, lo que haremos será barrer todas las hojas del libro y comprobar que el nombre Indice no existe. En caso de que exista, informaremos de ello mediante un MsgBox y terminaremos la ejecución del código sin hacer nada mas.

'Comprobar si existe indice
For Each Hoja In ActiveWorkbook.Sheets
     If Hoja.Name = "Indice" Then
          Mensaje = "Ya existe una hoja con el nombre Indice." & vbCrLf _
               &"Elimínala antes de crear el indice de forma automática."
               MsgBox Mensaje, vbExclamation + vbOKOnly, "ERROR"
          Exit Sub
     End If
Next Hoja

Paso 3. Preguntar si se quiere insertar una fila

Como hemos descrito antes, lo siguiente será preguntar al usuario si quiere insertar una fila en la cabecera de las hojas. Con esto, preservaremos los datos que pudiera haber allí.

 'Preguntar si insertar fila
 InsertarFila = MsgBox("Quieres insertar una fila en la cabecera de cada hoja?", vbYesNo, "INSERTAR FILA")

Paso 4. Crear la hoja Indice

Si hemos llegado hasta aquí sin salir en el paso anterior, lo siguiente que tendremos que hacer es insertar una hoja y hacer que se posiciones en primer lugar.

'Crear hoja Indice
     With ActiveWorkbook
          Set HojaIndice = .Sheets.Add(before:=.Sheets(1))
          HojaIndice.Name = "Indice"
     End With

     Fila = 1

     HojaIndice.Cells(Fila, 1).Value = "Indice"
     HojaIndice.Cells(Fila, 1).Font.Size = 14
     HojaIndice.Cells(Fila, 1).Font.Bold = True
     ActiveWindow.DisplayGridlines = False

Paso 5. Crear listado de hojas y vínculos

Ahora simplemente, iremos barriendo toda la colección de hojas del libro y, siempre que no sea la hoja Indice,

• Insertaremos la fila de cabecera de cada hoja, siempre que hayamos respondido Si (vbYes) a la pregunta
• Insertaremos el nombre de la hoja en el Indice y crearemos el hipervínculo a la misma. Fíjate que, además, se controla si la hoja es visible o está oculta, en cuyo caso, se indica añadiendo «(oculta)» a continuación del nombre de la hoja.
• Insertamos en la celda A1 de cada hoja el texto Volver con el hipervínculo que nos llevará de vuelta a la hoja Indice.

Este es el código necesario:

'Crear lista de hojas y vinculos
    For Each Hoja In ActiveWorkbook.Sheets
        If Hoja.Name <> "Indice" Then
            Fila = Fila + 1
            'Insertar cabecera
            If InsertarFila = vbYes Then
                Sheets(Hoja.Name).Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            End If
            'Controlar hojas ocultas
            If Sheets(Hoja.Name).Visible = xlSheetVisible Then
                HojaIndice.Hyperlinks.Add Anchor:=HojaIndice.Cells(Fila, 1), Address:="", SubAddress:= _
                    "'" & Hoja.Name & "'!A1", TextToDisplay:=Hoja.Name
            Else
                HojaIndice.Hyperlinks.Add Anchor:=HojaIndice.Cells(Fila, 1), Address:="", SubAddress:= _
                    "'" & Hoja.Name & "'!A1", TextToDisplay:=Hoja.Name & " - (Oculta)"
            End If
            Sheets(Hoja.Name).Hyperlinks.Add Anchor:=Sheets(Hoja.Name).Cells(1, 1), Address:="", _ 
                    SubAddress:= "Indice!A1", TextToDisplay:="Volver"
        End If
    Next Hoja

Paso 6. Configurar hoja de índice

En este último paso, configuramos el ancho de la columna índice y centramos el título y terminamos la sub.

Columns("A:A").ColumnWidth = 100
    HojaIndice.Range(Cells(1, 1), Cells(Fila, 1)).HorizontalAlignment = xlCenter
    
End Sub

Código completo

Option Explicit

Sub CrearIndice()
    Dim HojaIndice As Worksheet
    Dim Hoja As Worksheet
    Dim Fila As Integer
    Dim InsertarFila As Integer
    Dim Mensaje As String
    
    'Comprobar si existe indice
    For Each Hoja In ActiveWorkbook.Sheets
        If Hoja.Name = "Indice" Then
           Mensaje = "Ya existe una hoja con el nombre Indice." & vbCrLf _
            & "Elimínala antes de crear el indice de forma automática."
            MsgBox Mensaje, vbExclamation + vbOKOnly, "ERROR"
            Exit Sub
        End If
    Next Hoja

    
    'Preguntar si insertar fila
    InsertarFila = MsgBox("Quieres insertar una fila en la cabecera de cada hoja?", vbYesNo, "INSERTAR FILA")
    
    'Crear hoja Indice
    With ActiveWorkbook
        Set HojaIndice = .Sheets.Add(before:=.Sheets(1))
        HojaIndice.Name = "Indice"
    End With
    
    
    
    Fila = 1
    
    HojaIndice.Cells(Fila, 1).Value = "Indice"
    HojaIndice.Cells(Fila, 1).Font.Size = 14
    HojaIndice.Cells(Fila, 1).Font.Bold = True
    ActiveWindow.DisplayGridlines = False
    
    'Crear lista de hojas y vinculos
    For Each Hoja In ActiveWorkbook.Sheets
        If Hoja.Name <> "Indice" Then
            Fila = Fila + 1
            'Insertar cabecera
            If InsertarFila = vbYes Then
                Sheets(Hoja.Name).Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            End If
            'Controlar hojas ocultas
            If Sheets(Hoja.Name).Visible = xlSheetVisible Then
                HojaIndice.Hyperlinks.Add Anchor:=HojaIndice.Cells(Fila, 1), Address:="", SubAddress:= _
                    "'" & Hoja.Name & "'!A1", TextToDisplay:=Hoja.Name
            Else
                HojaIndice.Hyperlinks.Add Anchor:=HojaIndice.Cells(Fila, 1), Address:="", SubAddress:= _
                    "'" & Hoja.Name & "'!A1", TextToDisplay:=Hoja.Name & " - (Oculta)"
            End If
            Sheets(Hoja.Name).Hyperlinks.Add Anchor:=Sheets(Hoja.Name).Cells(1, 1), Address:="", _ 
                    SubAddress:= "Indice!A1", TextToDisplay:="Volver"
        End If
    Next Hoja

    Columns("A:A").ColumnWidth = 100
    HojaIndice.Range(Cells(1, 1), Cells(Fila, 1)).HorizontalAlignment = xlCenter
    
End Sub

Conclusión

Has visto, paso a paso, como con unas pocas líneas de código, hemos conseguido definir una rutina para crear una hoja índice automáticamente que nos puede ser muy útil y que podremos guardar en nuestro archivo de rutinas para reutilizar cuando nos covenga.

También puede ser una de las personalizaciones a añadir a la barra de acceso rápido si la incluimos en nuestro archivo de add-ins. Puedes ver como hacer esto en Personalizar Excel con un fichero de add-ins.

Por su puesto, se podría hacer incluyendo botones de retorno o una imagen superpuesta, en vez de un texto «Volver» en la celda A1, pero el objetivo era ver la forma de estructurar la rutina y de esta forma no hemos complicado el ejemplo excesivamente.

Ahora es tu turno de investigar estas opciones y modificar el código para incluirlas. Y si quieres comentar tus dudas o compartir tu código si lo consigues, no dudes en hacerlo a través del foro o en los comentarios.