Tabela dinâmica de atualização do Excel VBA

Quando inserimos uma tabela dinâmica na planilha, uma vez que os dados mudam os dados da tabela dinâmica não mudam, precisamos fazer isso manualmente, mas no VBA há uma instrução para atualizar a tabela dinâmica que é expression.refreshtable , usando isso podemos atualizar o tabela dinâmica referenciando a planilha que a consiste ou podemos nos referir a todas as tabelas dinâmicas nas planilhas e atualizá-las todas de uma vez.

A tabela dinâmica é vital para analisar a grande quantidade de dados. Ajuda diretamente na análise, resumo e também na interpretação de dados úteis. No entanto, um dos problemas com esta tabela dinâmica é que ela não será atualizada automaticamente se houver qualquer alteração nos dados de origem; o usuário deve atualizar a tabela dinâmica acessando a tabela dinâmica específica sempre que houver uma alteração. Mas diga adeus ao processo manual, porque aqui temos o método para atualizar a tabela dinâmica assim que você fizer qualquer alteração na tabela dinâmica.

Como atualizar automaticamente o código VBA dos dados da tabela dinâmica?

A única vez que a tabela dinâmica precisa ser atualizada é sempre que houver qualquer alteração nos dados de origem da tabela dinâmica à qual estamos nos referindo.

Por exemplo, observe os dados abaixo e a tabela dinâmica.

Agora vou mudar os números nos dados de origem, ou seja, de A1 para B17.

Na célula B9, tenho que alterar o valor de 499 para 1499, ou seja, 1000 de aumento nos dados, mas se você olhar para o pivô ainda mostra o resultado como 4295 em vez de 5295. Tenho que atualizar manualmente minha tabela dinâmica para atualizar a tabela dinâmica.

Para superar esse problema, precisamos escrever um código de macro simples do Excel para atualizar a tabela dinâmica sempre que houver qualquer alteração nos dados de origem.

Você pode baixar este modelo VBA Atualizar Tabela Dinâmica aqui - Modelo Excel VBA Atualizar Tabela Dinâmica

# 1 - Macro simples para atualizar toda a tabela

Etapa 1: evento de mudança da folha de dados

Precisamos acionar o evento de alteração da folha de dados. No editor visual basic, clique duas vezes na folha de dados.

Depois de clicar duas vezes na planilha, selecione “Planilha” e selecione o evento como “Alterar”.

Você verá um subprocedimento automático aberto como Worksheet_Change (ByVal Target As Range)

Etapa 2: usar objeto de planilha

Consulte a folha de dados usando o objeto Planilhas.

Etapa 3: consulte a tabela dinâmica por nome

Consulte o nome da tabela dinâmica pelo nome da tabela dinâmica.

Etapa 4: use o método de atualização da tabela

Selecione o método como “Atualizar Tabela”.

Agora, este código irá atualizar a tabela dinâmica “Tabela Dinâmica1” sempre que houver qualquer alteração na planilha de dados de origem. Você pode usar o código abaixo, basta alterar o nome da tabela dinâmica.

Código:

 Private Sub Worksheet_Change (ByVal Target As Range) Worksheet ("Data Sheet"). PivotTables ("PivotTable1"). RefreshTable End Sub

# 2 - Atualizar todas as tabelas dinâmicas da mesma planilha

Se você tiver muitas tabelas dinâmicas na mesma planilha, poderá atualizar todas as tabelas dinâmicas com um único clique. Use o código abaixo para atualizar todas as tabelas dinâmicas na planilha.

Código:

 Sub Refresh_Pivot_Tables_Example1 () Worksheets ("Data Sheet"). Selecione With ActiveSheet .PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable .PivotTables ("Table4"). RefreshTable .PivotTables ("Table5"). RefreshTable End With End Sub

Você precisa alterar o nome da planilha e os nomes da tabela dinâmica de acordo com os detalhes da planilha.

# 3 - Atualizar todas as tabelas na pasta de trabalho

É altamente improvável que tenhamos todas as tabelas dinâmicas na mesma planilha. Normalmente, para cada relatório, tentamos adicionar tabelas dinâmicas separadas em planilhas separadas. Nesses casos, não podemos continuar escrevendo o código para cada tabela dinâmica a ser atualizada.

Então, o que podemos fazer é com um único código usando loops, podemos percorrer todas as tabelas dinâmicas na pasta de trabalho e atualizá-las com um único clique no botão.

O código a seguir percorrerá cada tabela dinâmica e as atualizará.

Código 1:

 Sub Refresh_Pivot_Tables_Example2 () Dim PT As PivotTable For Each PT In ActiveWorkbook.PivotTables PT.RefreshTable Next PT End Sub

Código 2:

 Sub Refresh_Pivot_Tables_Example3 () Dim PC como PivotCache para cada PC em ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub

Ambos os códigos farão a atualização das tabelas dinâmicas.

Se você deseja que a tabela dinâmica seja atualizada assim que houver qualquer alteração na folha de dados da folha dinâmica, você precisa copiar e colar os códigos acima no evento Alteração da Planilha nessa pasta de trabalho.

# 4 - Evite o tempo de carregamento usando o evento de desativação da planilha

Quando usamos o evento “Alteração da planilha”, ele continua atualizando mesmo quando não há alteração na fonte de dados, mas se ocorrer alguma alteração na planilha.

Mesmo se você inserir um único ponto na planilha, ele tentará atualizar a tabela dinâmica. Portanto, para evitar isso, podemos usar o método “Worksheet Deactivate” ao invés do método “Worksheet Change”.