¿Cómo crear una lista de todos los nombres de hojas de cálculo de un libro de trabajo?

Imagina que tienes un libro de trabajo con varias hojas de cálculo y quieres listar automáticamente todos los nombres de las hojas, completos con hipervínculos clicables para navegar a cada hoja correspondiente. Este tutorial ofrece métodos rápidos y efectivos para generar una lista de nombres de hojas de cálculo en Excel.

Obtén una lista de todos los nombres de hojas de cálculo de un libro de trabajo dinámicamente con fórmulas

Obtén una lista de todos los nombres de hojas de cálculo de un libro de trabajo con Kutools para Excel

Obtén una lista de todos los nombres de hojas de cálculo de un libro de trabajo con código VBA

Obtén una lista de todos los nombres de hojas de cálculo de un libro de trabajo dinámicamente con fórmulas

En Excel, puedes definir un nombre de rango y luego aplicar una fórmula para listar todos los nombres de las hojas del libro de trabajo actual; sigue estos pasos:

1. Ve a hacer clic en "Fórmulas" > "Nombres", ver captura de pantalla:

2. En el cuadro de diálogo "Administrador de nombres", haz clic en el botón "Nuevo", ver captura de pantalla:

3. En el cuadro de diálogo emergente "Nuevo nombre", especifica un nombre en el cuadro de texto "Nombre" y luego copia la siguiente fórmula en el cuadro de texto "Se refiere a", ver captura de pantalla:

=GET.WORKBOOK(1)&T(NOW())

4. Luego haz clic en "Aceptar" > "Cerrar" para cerrar los cuadros de diálogo. Ahora, ve a una hoja donde quieras listar todos los nombres de las hojas y luego introduce la siguiente fórmula en una celda en blanco:

=IFERROR(INDEX(MID(Sheetnames,FIND("]",Sheetnames)+1,255),ROWS($A$2:A2)),"")

Nota: En la fórmula anterior, "Sheetnames" es el nombre de rango que creaste en el paso 3.

5. Y luego arrastra el controlador de relleno hacia abajo hasta que se muestren celdas en blanco, y ahora, todos los nombres de las hojas del libro de trabajo actual se han listado como se muestra en la siguiente captura de pantalla:

6. Si deseas crear el hipervínculo para cada hoja, utiliza la siguiente fórmula:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

Nota: En la fórmula anterior, "A2" es la celda que contiene el nombre de la hoja, y "A1" es la celda donde quieres que esté ubicada la celda activa. Por ejemplo, si haces clic en el texto del hipervínculo, se ubicará en la celda A1 de la hoja.

7. Ahora, cuando hagas clic en el texto del hipervínculo, te llevará a esa hoja, ver la siguiente demostración:

Obtén una lista de todos los nombres de hojas de cálculo de un libro de trabajo con Kutools para Excel

Con la utilidad "Crear tabla de enlace" del complemento de terceros "Kutools para Excel", puedes crear una lista de nombres de hojas de cálculo con un solo clic y vincular cada hoja con un hipervínculo.

1. Haz clic en "Kutools Plus" > "Hoja de Cálculo" > "Crear tabla de enlace", ver captura de pantalla:

2. En el cuadro de diálogo "Crear tabla de enlace", especifica la siguiente configuración:

(1.) Elige el "Estilo" que prefieras; puedes crear los nombres de las hojas de cálculo con hipervínculos o con botones de macro. (2.) Introduce los nombres de las hojas de cálculo para el índice de hojas. (3.) Especifica la ubicación del índice de hojas de cálculo. (4.) Especifica cuántas columnas deseas usar en la nueva hoja de cálculo para mostrar los nombres de las hojas de cálculo.

3. Después de finalizar la configuración, haz clic en "Aceptar". Todos los nombres de las hojas de cálculo se han listado con enlaces en una nueva hoja de cálculo del libro de trabajo actual. Ver captura de pantalla:

nombres de hojas de cálculo con hipervínculos	nombres de hojas de cálculo con botones de macro

Obtén una lista de todos los nombres de hojas de cálculo de un libro de trabajo con código VBA

1. Mantén presionadas las teclas "ALT" + "F11" y se abrirá la ventana "Microsoft Visual Basic para Aplicaciones".

2. Haz clic en "Insertar" > "Módulo" y pega la siguiente macro en la ventana "Módulo".

VBA: Lista todos los nombres de hojas de cálculo con hipervínculos en una nueva hoja de cálculo:

Sub CreateIndex()
'updateby Extendoffice
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Index").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Index"
    I = 1
    Cells(1, 1).Value = "INDEX"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Index" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

3. Presiona la tecla "F5" para ejecutar esta macro. Todos los nombres de hojas de cálculo del libro de trabajo activo ahora están listados en una nueva hoja de cálculo llamada "Índice", con hipervínculos que navegan a cada hoja respectiva. Ver la siguiente captura de pantalla:

Artículos relacionados