¿Cómo crear una lista dinámica de nombres de hojas de trabajo en Excel?

Si tiene varias hojas en un libro, y desea crear una lista dinámica de todos los nombres de las hojas en una nueva hoja del libro, ¿cómo puede hacerlo? Este tutorial presenta algunos trucos para completar rápidamente esta tarea en Excel.

Crear una lista dinámica de nombres de hojas de trabajo con Definir Nombre y Fórmula

Crear una lista dinámica de nombres de hojas de trabajo con código VBA

Crear una lista dinámica de nombres de hojas de trabajo con Kutools para Excel

Mostrar una lista dinámica de nombres de hojas de trabajo con Kutools para Excel

Crear una lista dinámica de nombres de hojas de trabajo con Definir Nombre y Fórmula

1. Seleccione una celda en una hoja en blanco, aquí selecciono A1, y luego haga clic en Fórmulas > Definir Nombre. Ver captura de pantalla:

2. Luego, en el cuadro de diálogo Nuevo Nombre escriba Hojas en el cuadro de texto Nombre (puede cambiarlo según sea necesario), y escriba esta fórmula =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","") en el cuadro de texto Se refiere a. Ver captura de pantalla:

3. Haga clic en Aceptar. Luego vaya a la celda seleccionada (A1) y escriba esta fórmula =INDEX(Hojas,FILAS($A$1:$A1)) (A1 es la celda donde escribe esta fórmula, "Hojas" es el nombre que definió en el Paso 2) en ella, luego arrastre el controlador de autollenado hacia abajo hasta que aparezca #¡REF!.

Consejo: Si se eliminan o agregan hojas de trabajo, debe ir a A1 y presionar la tecla Intro y luego arrastrar nuevamente el controlador de autollenado.

Crear una lista dinámica de nombres de hojas de trabajo con código VBA

Si desea crear una lista dinámica de nombres de hojas de trabajo que puedan vincularse a cada hoja, puede usar código VBA.

1. Cree una nueva hoja de trabajo y cámbiele el nombre a Índice. Ver captura de pantalla:

2. Haga clic derecho en el nombre de la hoja de Índice, seleccione Ver Código del menú contextual. Ver captura de pantalla:

3. En la ventana emergente, copie y pegue el siguiente código VBA en ella.

VBA: Crear lista dinámica de nombres de hojas de trabajo.

Private Sub Worksheet_Activate()
	'Updateby20150305
	Dim xSheet As Worksheet
	Dim xRow As Integer
	Dim calcState As Long
	Dim scrUpdateState As Long
	Application.ScreenUpdating = False
	xRow                       = 1
	With Me
		.Columns(1).ClearContents
		.Cells(1, 1) = "INDEX"
		.Cells(1, 1).Name = "Index"
	End With
	For Each xSheet In Application.Worksheets
		If xSheet.Name <> Me.Name Then
			xRow                     = xRow + 1
			With xSheet
				.Range("A1").Name = "Start_" & xSheet.Index
				.Hyperlinks.Add anchor: = .Range("A1"), Address: = "", _
				SubAddress:             = "Index", TextToDisplay: = "Back to Index"
			End With
			Me.Hyperlinks.Add anchor: = Me.Cells(xRow, 1), Address: = "", _
			SubAddress: = "Start_" & xSheet.Index, TextToDisplay: = xSheet.Name
		End If
	Next
	Application.ScreenUpdating = True
End Sub

4. Haga clic en Ejecutar o F5 para ejecutar el VBA, ahora se ha creado una lista dinámica de nombres de hojas de trabajo.

Consejo:

1. Cuando se elimina o inserta una hoja de trabajo del libro, la lista de nombres de las hojas de trabajo cambiará automáticamente.

2. Puede hacer clic en el nombre de la hoja de la lista de nombres para ir a la hoja.

Con los dos métodos anteriores no son lo suficientemente convenientes, si está interesado en nuevas utilidades, puede pasar a los siguientes dos métodos que pueden facilitar aún más esta tarea.

Crear una lista dinámica de nombres de hojas de trabajo con Kutools para Excel

 Si solo desea enumerar rápidamente todos los nombres de las hojas de trabajo del libro y vincularlos a las hojas originales, puede usar Crear Lista de Nombres de Hojas de Kutools para Excel.

Después de instalar gratuitamente Kutools para Excel, por favor haga lo siguiente:

1. Haga clic en Kutools Plus > Hoja de Trabajo > Crear Lista de Nombres de Hojas. Ver captura de pantalla:

2. En el cuadro de diálogo Crear Lista de Nombres de Hojas:

 

(1) Marque el estilo de índice que necesita en Estilo de Índice de Hojas sección;
(2) Asigne un nombre al nuevo índice de hojas escribiendo los datos en Nombre de la tabla de enlace cuadro de texto;
(3) Especifique la ubicación donde colocará el índice añadido en la Ubicación en el libro de trabajo lista;
(4) Si desea mostrar los nombres de las hojas en una sola lista, seleccione 1 columna en Número de filas a abarcar lista.

3. Haga clic en Aceptar. Ahora puede ver que los nombres de las hojas están listados.

Consejos:

1. Puede hacer clic en el nombre de la hoja para cambiar rápidamente a su hoja original.

2. La lista o los nombres de las hojas no pueden cambiar dinámicamente con la inserción o eliminación de hojas.

3. De hecho, también puede crear una lista de botones para vincular a hojas relativas, solo necesita marcar Crear botones y macros en el cuadro de diálogo. Ver captura de pantalla:

Haga clic aquí para obtener más información sobre Crear Lista de Nombres de Hojas.

  Crear Lista Clicable de Nombres de Hojas

Mostrar una lista dinámica de nombres de hojas de trabajo con Kutools para Excel

Si utiliza Kutools para Excel, también puede usar la utilidad Navegación para mostrar los nombres de las hojas de trabajo enlazables en un panel

Después de instalar gratuitamente Kutools para Excel, por favor haga lo siguiente:

1. Haga clic en Kutools > Navegación. Haga clic en Libro de Trabajo y Hoja para mostrar el libro y la hoja de trabajo, y puede seleccionar un libro, luego sus hojas de trabajo se mostrarán en el panel de Navegación . Ver captura de pantalla:
   

Consejo:

Cuando se eliminan o agregan hojas de trabajo, puede hacer clic en el botón de actualización  en el Navegaciónpanel para actualizar los nombres de las hojas de trabajo.

Haga clic aquí para obtener más información sobre Navegación.

  Panel de Navegación -- Listar Hojas