¿Cómo crear una lista con los nombres de todas las hojas de cálculo de un libro?

Imagine que tiene un libro con varias hojas de cálculo y quiere generar automáticamente una lista con los nombres de todas ellas, incluyendo hipervínculos en los que pueda hacer clic para navegar directamente a cada hoja. Este tutorial le ofrece métodos rápidos y eficaces para crear una lista de nombres de hojas en Excel.

Obtenga una lista de todas las Nombre de la hoja de cálculo de un libro dinámicamente mediante fórmulas

Obtenga una lista de todas las Nombre de la hoja de cálculo de un libro con Kutools para Excel

Obtenga una lista de todas las Nombre de la hoja de cálculo de un libro con código VBA

Obtenga una lista de todas las Nombre de la hoja de cálculo de un libro dinámicamente mediante fórmulas

En Excel, puede definir un nombre de celda y luego aplicar una fórmula para listar todos los nombres de las hojas del libro de trabajo actual. Siga estos pasos:

1. Haga clic en «Fórmulas» > «Administrador de nombres»; consulte la captura de pantalla:

2. En el cuadro de diálogo «Administrador de nombres», haga clic en el botón «Nuevo» (consulte la captura de pantalla).

3. En el cuadro de diálogo emergente «Nuevo nombre», introduzca un nombre en el campo «Nombre» y copie la siguiente fórmula en el campo «Hace referencia a», tal como se muestra en la captura de pantalla:

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

4. A continuación, haga clic en «Aceptar» > «Cerrar» para cerrar los cuadros de diálogo. Luego, vaya a la hoja en la que desee listar todos los nombres de las hojas e introduzca la siguiente fórmula en una celda vacía:

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

Nota: En la fórmula anterior, «Sheetnames» es el nombre de celda que creó en el paso 3.

5. Luego, arrastre el controlador de relleno hacia abajo hasta que aparezcan celdas vacías. Ahora, todos los nombres de las hojas del libro de trabajo actual se muestran listados, tal como se aprecia en la siguiente captura de pantalla:

6. Si desea crear un hipervínculo para cada hoja, utilice 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 en la que desea colocar el cursor activo. Por ejemplo, al hacer clic en el hipervínculo, se situará en la celda A1 de dicha hoja.

7. Ahora, al hacer clic en el texto del hipervínculo, accederá directamente a esa hoja. Consulte la siguiente demostración:

Obtenga una lista de todas las Nombre de la hoja de cálculo de un libro con Kutools para Excel

Con la utilidad «Crear tabla de enlace» del complemento de terceros «Kutools para Excel», podrá generar con un solo clic una lista con los nombres de las hojas de cálculo e incluir un hipervínculo a cada una.

1. Haga clic en «KUTOOLS PLUS» > «Hoja de cálculo» > «Crear tabla de enlace», tal como se muestra en la captura de pantalla:

2. En el cuadro de diálogo «Crear tabla de enlace», configure los siguientes parámetros:

(1.) Elija el «Estilo» que desee; podrá crear el nombre de la hoja de cálculo con hipervínculos o con botones de macro. (2.) Introduzca el nombre de la hoja de cálculo que servirá como índice de hojas. (3.) Especifique dónde desea ubicar el índice de hojas de cálculo. (4.) Indique cuántas columnas desea usar en la nueva hoja de cálculo para mostrar el nombre de la hoja.

3. Tras finalizar la configuración, haga clic en «Aceptar». Todos los nombres de las hojas de cálculo se han listado con vínculos en una nueva hoja del libro de trabajo actual. Consulte la captura de pantalla:

Nombre de la hoja de cálculo con hipervínculos	Nombre de la hoja de cálculo con botones de macro

Obtenga una lista de todas las Nombre de la hoja de cálculo de un libro con código VBA

1. Mantenga pulsadas las teclas «ALT» + "F11" para abrir la ventana de Microsoft Visual Basic para Aplicaciones.

2. Haga clic en «Insertar» > «Módulo» y pegue la siguiente macro en la ventana del módulo.

VBA: Liste todas las Nombre de la hoja de cálculo con hipervínculos en una Hoja de cálculo nueva:

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. Pulse la tecla "F5" para ejecutar esta macro. Todos los nombres de las hojas de cálculo del libro de trabajo actual aparecerán ahora en una nueva hoja de cálculo llamada «Índice», con hipervínculos que le permitirán navegar directamente a cada hoja correspondiente. Consulte la siguiente captura de pantalla:

Artículos relacionados