Como criar um sumário para todas as planilhas e atualizações automaticamente?

Supondo que você tenha uma pasta de trabalho que contém centenas de planilhas, navegar para uma planilha específica entre várias planilhas pode causar dor de cabeça para a maioria de nós. Nesse caso, criar um sumário para essas planilhas pode ajudá-lo a acessar a determinada planilha de maneira rápida e fácil. Este tutorial falará sobre como criar um índice para todas as planilhas e atualizar a tabela automaticamente ao inserir, excluir ou alterar os nomes das planilhas.

Crie um índice para todas as planilhas e atualize automaticamente com fórmulas

Crie um índice para todas as planilhas e atualizações automaticamente com um código VBA

Crie um índice para todas as planilhas e atualizações automaticamente com um recurso poderoso

Crie um índice para todas as planilhas e atualize automaticamente com fórmulas

No Excel, você pode criar um índice definindo um nome de intervalo e referenciando o nome em uma fórmula, siga as etapas a seguir:

1. Insira uma nova planilha antes de todas as planilhas nas quais deseja criar o sumário e renomeie-o conforme necessário.

2. Então clique Fórmula > Definir o Nome, veja a captura de tela:

3. No Novo nome caixa de diálogo, especifique um nome Lista de folhas no Nome caixa, você pode alterá-lo para o seu próprio e, em seguida, insira esta fórmula: = GET.WORKBOOK (1) & T (NOW ()) no Refere-se a caixa de texto, veja a captura de tela:

4. Então clique OK para fechar a caixa de diálogo.

5. Agora, vá para a nova planilha onde você deseja criar um índice e insira a seguinte fórmula em uma célula em branco:

6. E, em seguida, arraste a alça de preenchimento para as células até que as células em branco sejam exibidas e todos os nomes de planilhas (incluindo as planilhas ocultas) da pasta de trabalho atual tenham sido listados conforme a captura de tela abaixo:

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

8. Agora, ao clicar no texto do hiperlink, ele irá para essa planilha de uma só vez. E se você inserir uma nova planilha, excluir uma planilha ou alterar o nome da planilha, o conteúdo da tabela será atualizado automaticamente.

Crie um índice para todas as planilhas e atualizações automaticamente com um código VBA

Às vezes, você não precisa exibir as planilhas ocultas no conteúdo da tabela. Para resolver esse problema, o código VBA a seguir pode lhe fazer um favor.

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

2. No aberto Microsoft Visual Basic para Aplicações janela, copie e cole o código abaixo na janela de código da planilha:

Código VBA: crie um índice para todas as planilhas e atualizações 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. Então aperte F5 key para executar este código, e o conteúdo da tabela é criado de uma só vez na nova planilha, todas as planilhas ocultas não serão exibidas no conteúdo da tabela, veja a captura de tela:

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

Crie um índice para todas as planilhas e atualizações automaticamente com um recurso poderoso

Se você tem Kutools for Excel, as TIC Painel de navegação pode ajudá-lo a listar todos os nomes de planilhas em um painel esquerdo verticalmente e navegar até a planilha específica de maneira rápida e fácil.

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

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

2. No expandido Navegação painel, clique em Livro e Folha de Trabalho ícone, todas as pastas de trabalho abertas são listadas na caixa de listagem superior e todas as planilhas visíveis na pasta de trabalho são exibidas na caixa de listagem inferior, veja a captura de tela:

3. E agora, você pode ir para a planilha apenas clicando no nome da planilha no painel esquerdo. E quando você excluir, inserir ou renomear uma planilha, a lista de planilhas no painel será atualizada dinamicamente.