Ir para o conteúdo principal

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

Supondo que você tenha uma pasta de trabalho com várias planilhas, agora você deseja listar todos os nomes de planilhas na pasta de trabalho atual, existe algum método rápido para criar uma lista desses nomes de planilhas no Excel sem digitá-los um por um? Este tutorial fornece instruções sobre como listar 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 um recurso útil

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 seguintes etapas:

1. Clique em Fórmula > Gerente de Nomes, veja a captura de tela:

2. No Gerente de Nomes caixa de diálogo, clique em Novo botão, veja a captura de tela:

3. No estalou para fora Novo nome caixa de diálogo, especifique um nome no Nome caixa de texto e, em seguida, copie a fórmula abaixo para o Refere-se a caixa de texto, veja a captura de tela:

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

4. Então clique OK > Fechar para fechar as caixas de diálogo, agora, vá para uma planilha onde deseja listar todos os nomes das planilhas e, a seguir, insira a seguinte fórmula em uma célula em branco:

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

Note: Na fórmula acima, Nomes de folha é o nome do intervalo que você criou na etapa 3.

5. E, em seguida, arraste a alça de preenchimento para baixo para as células quando as células em branco forem exibidas, e agora, todos os nomes de planilhas da pasta de trabalho atual foram listados conforme a captura de tela abaixo:

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

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

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

7. Agora, quando você clicar no texto do hiperlink, ele o levará para aquela planilha, veja a demonstração abaixo:

Tips:
  • 1. Com as fórmulas acima, os nomes das planilhas criadas são listados dinamicamente. Quando você altera o nome da planilha na pasta de trabalho, o nome da planilha de índice é atualizado automaticamente.
  • 2. Você deve salvar o arquivo como Pasta de trabalho habilitada para macro do Excel formato, se quiser que as fórmulas funcionem bem depois que o arquivo for fechado e reaberto.

Obtenha uma lista de todos os nomes de planilhas de uma pasta de trabalho com um recurso útil

Com o Criar lista de nomes de planilhas utilidade do suplemento de terceiros Kutools for Excel, você pode criar uma lista de nomes de planilhas com um clique e criar um link para cada planilha com um hiperlink.

Observação:Para aplicar isso Criar lista de nomes de planilhas, em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique o recurso de forma rápida e fácil.

Depois de instalar Kutools for Excel, por favor, faça o seguinte:

1. Clique Kutools Plus > Planilha > Criar lista de nomes de planilhas, veja a captura de tela:

2. No Criar lista de nomes de planilhas caixa de diálogo, especifique as seguintes configurações:

(1.) Escolha estilos de índice de folha você gosta, você pode criar os nomes da planilha com hiperlinks ou com botões de macro.

(2.) Insira os nomes da planilha para o índice da planilha.

(3.) Especifique a localização do índice da planilha.

(4.) Especifique quantas colunas deseja usar na nova planilha para exibir os nomes da planilha.

3. Depois de terminar as configurações, clique em OK. Todos os nomes de planilhas foram listados com links em uma nova planilha da pasta de trabalho atual. Veja a imagem:

nomes de planilhas com hiperlinks nomes de planilhas com botões macro
Dica: Para usar esse recurso, você deve instalar Kutools for Excel primeiro, por favor clique para baixar e ter um teste gratuito de 30 dias !

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

1. Segure o ALT + F11 chaves, e abre o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole a macro a seguir no Módulo Janela.

VBA: Liste todos os nomes de planilhas com hiperlinks 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. aperte o F5 chave para executar esta macro. Agora todos os nomes de planilhas na pasta de trabalho ativa foram listados em uma nova planilha chamada Índice, e os nomes das planilhas também estão vinculados a cada planilha, veja a captura de tela:

Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
First, thanks to the author. And adding the Czech version.

tp 3. =O.KNIZE(1)&T(NYNÍ())
tp 4. =IFERROR(INDEX(ČÁST(nazvylistu;NAJÍT("]";nazvylistu)+1;255);ŘÁDKY($A$2:A4));"")
This comment was minimized by the moderator on the site
Hello,

je viens de tester la méthode via macros (dynamique avec formules) et ça marche au poil donc je tiens vraiment à remercier l'auteur de cet article parce que ça va vraiment m'aider dans mon travail ! Juste, je me permet de corriger les formules pour la version française. Et alors je ne sais pas si c'est parce que je suis sous la version 2019 mais Excel rouspète quand il n'y a pas d'argument en 3ème position de la fonction STXT ("MID" en version anglaise) donc obligé d'en rajouter un. Donc voilà ce que ça donne :

=LIRE.CLASSEUR(1)&T(MAINTENANT())

=SIERREUR(INDEX(STXT(nomsFeuilles;TROUVE("]";nomsFeuilles)+1,255;20);LIGNES($A$2:A2));"")

Bon travail à tous ! ;-)
This comment was minimized by the moderator on the site
Hello, Gizmil
Thank you for your comment, there are some functions are only available for English in Excel.
Your formula may help others.
Thanks again!
This comment was minimized by the moderator on the site
I close and open my document and all values in my sheet names column are all gone and blank but still the formula is there. I tried entering the same formula but it doesn't show the value anymore
This comment was minimized by the moderator on the site
Hello, Anne,
Sorrry for replying late, after creating the range names and formulas, you should save the workbook as Excel Macro-Enabled Workbook format, so next time, when you open the Excel file,the formulas can work well.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
I tried this one and it works. But when I close and open the file again all the values in my sheet names are blank and gone but the formula is still there. I tried enteing the same formula again but it doesn't show the value anymore
This comment was minimized by the moderator on the site
BRILLIANT!! Thank you so much! 😊
This comment was minimized by the moderator on the site
Causes problems when document protection is enabled by email or corporate policy
This comment was minimized by the moderator on the site
Thanks so much, this worked great.
This comment was minimized by the moderator on the site
Works great!! Thank you!!!!
This comment was minimized by the moderator on the site
sooooo helpful, works as expected!!!!! Thanks
This comment was minimized by the moderator on the site
THANK YOU SO MUCH! I freaking love your website. In a matter of minutes I've had a ton of time saved with two sections of this site including this one. Love it!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations