Agregar índice hojas EXCEL de un libro

Algunas veces, nuestros trabajos en EXCEL nos obliga a crear un número importante de hojas dentro de un libro.

Cuándo esto ocurre se nos hace muy incomodo el poder acceder a una determinada hoja, puesto que para buscarla debemos ir pulsando los botones de desplazamiento de hojas. Esto hace que la búsqueda de la hoja que deseamos sea lenta y tediosa.

A continuación presentamos una macro (o un procedimiento) que nos permitirá solucionar de una forma bastante elegante éste problema. Esta macro, nos añadirá automáticamente una hoja llamada “RESUMEN”, la cuál contendrá la lista de todas las hojas de nuestro libro, creando además un enlace ha ésta. El aspecto de la hoja resumen que nos añadirá es el siguiente:

De este modo, cuándo deseemos acceder a una hoja del libro, bastará con que nos situemos en la hoja “RESUMEN” y hagamos clic sobre el nombre de la hoja a la que deseamos acceder.

En este procedimiento utilizaremos la función SheetExists que nos permitirá conocer si una hoja Excel existe.

Para crear nuestra macro o procedimiento seguiremos los siguientes pasos:

• Abriremos el libro EXCEL en el cuál deseamos añadir nuestra macro.


• En el menú de EXCEL seleccionaremos Herramientas / Macro / Editor de Visual Basic


• Nos aparecerá el editor de Visual Basic (VBA, Visual Basic for Applications)


• En el menú del Editor seleccionaremos la opción Insertar / Módulo.


• Esto nos añadirá un nuevo elemento en el área Proyecto – VBAProject


• Ahora escribiremos el siguiente código dentro del módulo que hemos creado (Modulo1).
  

Public Sub AddResumeSheets()

Dim CellCnt As Integer

Dim Sht As Object

Dim ListPos As Integer

' Verificamos si la hoja de Resumen de Hojas existe,

' sino es así, la creamos

If Not SheetExists("Resumen") Then

Sheets.Add

ActiveSheet.Select

ActiveSheet.Name = "Resumen"

End If

' Ponemos la hoja resume la primera de todas

Sheets("Resumen").Move
Before:=Sheets(1)

' Borramos todo el contenido de la hoja RESUMEN

Cells.Select

Selection.ClearContents

Columns("A:A").ColumnWidth = 70 ' Ajustamos ancho columna 'A'

' Añadir listado de las hojas EXCEL del Libro

Range("A1").Select

ActiveCell.Value ="LISTADO DE LAS HOJAS EXCEL DEL LIBRO"

With Selection.Font

.Name = "Arial"

.Size = 16

.Bold = True

End With

' Recorremos todas las hojas del libro

CellCnt = 3

For Each Sht In ActiveWorkbook.Sheets

' Añadimos en el resumen todas las hojas a excepción de la hoja

' RESUMEN que hemos creado

If Sht.Name <> ActiveSheet.Name Then

If TypeName(Sht) = "Worksheet" Then

If Sht.Visible = True Then
Range("A" & CellCnt).Select

ActiveCell.FormulaR1C1 = Sht.Name

ActiveSheet.Hyperlinks.Add _

Anchor:=Selection, _

Address:="", _

SubAddress:= "'" & Sht.Name & "'!A1", _

TextToDisplay:=Sht.Name

End If

End If

CellCnt = CellCnt + 1

End If

Next Sht

End Sub

Private Function SheetExists(strSheetName As String) As Boolean

On Error Resume Next

Dim objSheet As Object

' Seleccionamos la hoja de cálculo
Set objSheet = ActiveWorkbook.Sheets(strSheetName)

' Comprobamos si existe o no la hoja

If Err = 0 Then

SheetExists = True

Else

SheetExists = False

End If

End Function

• Una vez añadido todo el código en el módulo que hemos insertado, será necesario que nada más abrir el libro se ejecute la macro para que nos añada la hoja resumen, para ello seleccionaremos el elemento ThisWorkbook en el área Proyecto – VBAProject, y haremos doble click para añadir el siguiente código:

Private Sub Workbook_Open()

' Añadimos la hoja RESUMEN con el listado de las hojas EXCEL

' del LIBRO

AddResumeSheets

End Sub

• A continuación, y antes de continuar, guardaremos el trabajo realizado. Para ello iremos al menú del editor de Visual Basic y seleccionaremos Archivo / Guardar.


• Una vez guardado, saldremos del editor de Visual Basic, seleccionando las opciones Archivo / Cerrar y volver a Microsoft Excel.

En estos momentos, nuestra hoja EXCEL ya dispone de una macro que nos facilitará la búsqueda de hojas dentro de nuestro Libro de trabajo.