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.