Como criar uma lista de todos os nomes de planilhas de uma pasta de trabalho?

Imagine que você tenha uma pasta de trabalho com várias planilhas e deseja listar todos os nomes das planilhas automaticamente, completos com hiperlinks clicáveis para navegar até cada planilha correspondente. Este tutorial oferece métodos rápidos e eficazes para gerar uma lista de nomes de planilhas no Excel.

Obtenha uma lista de todos os nomes de planilhas de uma pasta de trabalho dinamicamente com fórmulas

Obtenha uma lista de todos os nomes de planilhas de uma pasta de trabalho com o Kutools para Excel

Obtenha uma lista de todos os nomes de planilhas de uma pasta de trabalho com código VBA

Obtenha uma lista de todos os nomes de planilhas de uma pasta de trabalho dinamicamente com fórmulas

No Excel, você pode definir um nome de intervalo e, em seguida, aplicar uma fórmula para listar todos os nomes de planilhas da pasta de trabalho atual; siga as etapas abaixo:

1. Vá em "Fórmula" > "Gerenciador de Nomes", veja a captura de tela:

2. Na caixa de diálogo "Gerenciador de Nomes", clique no botão "Novo", veja a captura de tela:

3. No pop-up "Novo Nome", especifique um nome na caixa de texto "Nome" e, em seguida, copie a fórmula abaixo na caixa de texto "Refere-se a", veja a captura de tela:

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

4. Em seguida, clique em "OK" > "Fechar" para fechar as caixas de diálogo. Agora, vá para uma planilha onde você deseja listar todos os nomes de planilhas e insira a seguinte fórmula em uma célula em branco:

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

Nota: Na fórmula acima, "Sheetnames" é o nome do intervalo criado na etapa 3.

5. Depois, arraste a alça de preenchimento para baixo até que células em branco sejam exibidas, e agora todos os nomes de planilhas da pasta de trabalho atual foram listados conforme mostrado na captura de tela abaixo:

6. Se você quiser criar o hyperlink para cada planilha, use a fórmula abaixo:

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

Nota: Na fórmula acima, "A2" é a célula que contém o nome da planilha, e "A1" é a célula onde você quer que a célula ativa seja localizada. Por exemplo, se você clicar no texto do hyperlink, ele localizará a célula A1 da planilha.

7. Agora, quando você clica no texto do hyperlink, ele o levará para essa planilha, veja a demonstração abaixo:

Obtenha uma lista de todos os nomes de planilhas de uma pasta de trabalho com o Kutools para Excel

Com o utilitário "Criar Lista de Nomes de Planilhas" do complemento de terceiros "Kutools para Excel", você pode criar uma lista de nomes de planilhas com um único clique e vincular cada planilha com um hyperlink.

1. Clique em "Kutools Plus" > "Planilha" > "Criar Lista de Nomes de Planilhas", veja a captura de tela:

2. Na caixa de diálogo "Criar Lista de Nomes de Planilhas", especifique as seguintes configurações:

(1.) Escolha o "estilo de índice de planilhas" que você gosta; você pode criar os nomes das planilhas com hyperlinks ou com botões de macro. (2.) Insira os nomes das planilhas para o índice de planilhas. (3.) Especifique a localização do índice de planilhas. (4.) Especifique quantas colunas você deseja usar na nova planilha para exibir os nomes das planilhas.

3. Após concluir as configurações, clique em "OK". Todos os nomes das planilhas foram listados com links em uma nova planilha da pasta de trabalho atual. Veja a captura de tela:

nomes de planilhas com hyperlinks	nomes de planilhas com botões de macro

Obtenha uma lista de todos os nomes de planilhas de uma pasta de trabalho com código VBA

1. Mantenha pressionadas as teclas "ALT" + "F11" e abrirá a janela "Microsoft Visual Basic for Applications".

2. Clique em "Inserir" > "Módulo" e cole a seguinte macro na janela "Módulo".

VBA: Liste todos os nomes de planilhas com hyperlinks em uma nova planilha:

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. Pressione a tecla "F5" para executar esta macro. Todos os nomes de planilhas da pasta de trabalho ativa agora estão listados em uma nova planilha chamada "Índice", com hyperlinks que navegam para cada planilha respectiva. Veja a captura de tela abaixo:

Artigos relacionados