Crear un índice de todas las hojas con macros

Hola!!!

Eres de los que utiliza diariamente archivos de Excel con demasiadas hojas??

Te demoras ir de hoja en hoja buscando lo que necesitas?

Para eso que mejor que crear automáticamente un índice con simplemente ejecutar una función, ¿verdad?

Para esto te paso un código que te sirve para crear una hoja «Indice«, en donde se realiza una lista de todas las hojas que tiene el archivo de Excel con un formato agradable.

Código para crear un índice de todas las hojas automáticamente

Sub crearIndice()
    Dim indice As Worksheet
    On Error Resume Next
    Set indice = Worksheets("Indice")
    On Error GoTo 0
    
    Call crear_pagina_indice    'Crear la pagina Indice
    
    Dim fila As Long
    Dim vinculoRegreso As String
    
    fila = 5
    'Celda donde se colocará el hipervinculo de regreso al indice
    vinculoRegreso = "C1"
    For Each hoja In Worksheets
        If hoja.Name <> "Indice" Then
            With Sheets("Indice")
                .Hyperlinks.Add Anchor:=.Range("D" & fila), _
                Address:="", _
                SubAddress:="'" & hoja.Name & "'!A1", _
                TextToDisplay:=hoja.Name
            End With
            Sheets("Indice").Range("F" & fila).Value = "[Aca va la descripción de la hoja]"
            fila = fila + 2
        End If
    Next
    Sheets("Indice").Select
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Size = 14
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("A1").Select
    ActiveWorkbook.Sheets("Indice").Tab.Color = 65535
End Sub

Sub crear_pagina_indice()
    Worksheets.Add(Before:=Worksheets(1)).Name = "Indice"
    Range("C2:H2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Merge
    End With
    ActiveCell.FormulaR1C1 = "INDICE"
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    
    Selection.Font.Size = 24
    Selection.Font.Bold = True
    Range("A3").Select
    ActiveWindow.FreezePanes = True
End Sub

Este sería el resultado de ejecutar el código.

Cada uno de los nombres en la columna D tiene el hipervínculo hacia la respectiva hoja

Pasos para ejecutar la función

Al descargar el archivo de ejemplo en la parte de abajo de este artículo y abrirlo, vez que no tienes ningún botón para ejecutar la función?, esto es por que necesitas ejecutarlo desde el código.

Simplemente debes ubicarte en la primera función «crearIndice» y ejecutarla con la tecla F5.

Esto ejecuta la función completa, por que te explico esto? por que la idea es que copies el código en el archivo de Excel que necesites para crear el indice, y ejecutas la función tal cual te comento.

Con esto ya se crea la hoja de indice sin ningún problema!

DESCARGAR EJEMPLO

En el siguiente link puedes descargar un sencillo ejemplo, si tienes alguna duda dirígete al formulario de Contacto

Espero te guste, si te gustaría que hiciera algún ajuste al código me avisas!

Saludes!