Como criar um sumário para todas as planilhas e atualizá-lo automaticamente?

Suponha que você tenha uma pasta de trabalho que contenha centenas de planilhas. Navegar até uma planilha específica entre várias pode ser um desafio para a maioria de nós. Nesse caso, criar um sumário para essas planilhas pode ajudá-lo a acessar rapidamente e facilmente a planilha desejada. Este tutorial abordará como criar um sumário para todas as planilhas e atualizar a tabela automaticamente ao inserir, excluir ou alterar os nomes das planilhas.

Use fórmulas para criar um sumário para todas as planilhas e atualizá-lo automaticamente.

Use o Kutools para Excel para criar um sumário para todas as planilhas e atualizá-lo automaticamente.

Use código VBA para criar um sumário para todas as planilhas e atualizá-lo automaticamente.

Use fórmulas para criar um sumário para todas as planilhas e atualizá-lo automaticamente.

No Excel, você pode criar um sumário definindo um nome de intervalo e referenciando esse nome em uma fórmula. Siga os passos abaixo:

1. Insira uma nova planilha antes de todas as outras onde deseja criar o sumário e renomeie-a conforme necessário.

2. Em seguida, clique em "Fórmula" > "Definir Nome", veja a captura de tela:

3. Na caixa de diálogo "Novo Nome", especifique um nome "Sheetlist" no campo "Nome", você pode alterá-lo conforme desejar, e depois insira a seguinte fórmula no campo "Refere-se a":

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

4. Depois, clique no botão "OK" para fechar a caixa de diálogo.

5. Agora, vá para a nova planilha onde deseja criar o sumário e insira a seguinte fórmula em uma célula em branco:

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

6. E arraste a alça de preenchimento para baixo até que células em branco sejam exibidas, e todos os nomes das planilhas (incluindo as planilhas ocultas) da pasta de trabalho atual serão listados, como mostrado na captura de tela abaixo:

7. Em seguida, você deve criar hiperlinks para o conteúdo da tabela. Por favor, use a fórmula abaixo:

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

8. Agora, ao clicar no texto do hyperlink, você será direcionado imediatamente para essa planilha. E se você inserir uma nova planilha, excluir uma planilha ou alterar o nome da planilha, o conteúdo da tabela será atualizado automaticamente.

Use o Kutools para Excel para criar um sumário para todas as planilhas e atualizá-lo automaticamente.

Se você tiver o "Kutools para Excel", seu "Painel de Navegação" pode ajudá-lo a listar todos os nomes das planilhas em um painel à esquerda verticalmente e navegar até a planilha específica rapidamente e facilmente.

Após instalar o Kutools para Excel, faça o seguinte:

1. Clique em "Kutools" > "Navegação", veja a captura de tela:

2. No painel "Navegação" expandido, clique no ícone "Pasta de Trabalho e Planilha", todas as pastas de trabalho abertas são listadas na caixa de lista superior, e todas as planilhas visíveis dentro da pasta de trabalho são exibidas na caixa de lista inferior, veja a captura de tela:

3. E agora, você pode ir para a planilha apenas clicando no nome da planilha no painel à esquerda. E quando você exclui, insere ou renomeia uma planilha, a lista de planilhas no painel será atualizada dinamicamente.

Use código VBA para criar um sumário para todas as planilhas e atualizá-lo automaticamente.

Às vezes, você não precisa exibir as planilhas ocultas no conteúdo da tabela. Para resolver esse problema, o seguinte código VBA pode ajudá-lo.

1. Insira uma nova planilha antes de todas as outras onde deseja criar o sumário e renomeie-a conforme necessário. Em seguida, clique com o botão direito do mouse na guia da planilha e escolha "Visualizar Código" no menu de contexto, veja a captura de tela:

2. Na janela aberta "Microsoft Visual Basic for Applications", copie e cole o código abaixo na janela de código da planilha:

Código VBA: Criar um sumário para todas as planilhas e atualizá-lo automaticamente.

Private Sub Worksheet_Activate()
'Updateby ExtendOffice
Dim xWsh As Worksheet
Dim xWshs As Worksheets
Dim xShowHinddenWorkSheet As Boolean
Dim xI As Long
Dim xRg As Range
Dim xStrTitle, xStrTCHeader, xStrWShName As String
xShowHinddenWorkSheet = False 'Change this to True to display the hidden sheets as you need
xStrTitle = "A1"
xStrTCHeader = "A3"
On Error Resume Next
Application.ScreenUpdating = False
Me.Cells.Clear
Me.Range(xStrTitle).Font.Bold = True
Me.Range(xStrTitle).Font.Size = Me.Range(xStrTitle).Font.Size + 2
Me.Range(xStrTitle).Value = "Table of Contents"
Me.Range(xStrTCHeader).Value = "No."
Me.Range(xStrTCHeader).Offset(0, 1).Value = "Sheet Name"
Me.Range(xStrTCHeader).Resize(1, 2).Font.Bold = True
xStrWShName = Me.Name
xI = 1
For Each xWsh In Application.ActiveWorkbook.Worksheets
    If xWsh.Name <> xStrWShName Then
        If (xWsh.Visible = xlSheetVisible) Or xShowHinddenWorkSheet Then
            Me.Hyperlinks.Add Anchor:=Me.Range(xStrTCHeader).Offset(xI, 1), Address:="", SubAddress:="'" & xWsh.Name & "'!A1", TextToDisplay:=xWsh.Name
            Me.Range(xStrTCHeader).Offset(xI).Value = xI
            xI = xI + 1
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub

3. Depois, pressione a tecla "F5" para executar este código, e o conteúdo da tabela será criado imediatamente na nova planilha, todas as planilhas ocultas não serão exibidas no conteúdo da tabela, veja a captura de tela:

4. A partir de agora, ao excluir, inserir ou renomear uma planilha, o conteúdo da tabela será atualizado dinamicamente.