Conclusão: Aprenda a usar uma macro simples para atualizar tabelas dinâmicas automaticamente sempre que forem feitas alterações nos dados de origem. Eu também compartilho uma solução não macro para atualizar as tabelas dinâmicas quando o arquivo é aberto. Inclui tutorial em vídeo e download de arquivo do Excel.
Nível de habilidade: intermediário
Baixe o arquivo do Excel
Se você aprender melhor fazendo isso sozinho, poderá baixe o arquivo que estou usando no vídeo para acompanhar. Aqui está o arquivo Excel que contém o código VBA.
Atualizar tabela dinâmica automaticamente.xlsm (41,5 KB)
Atualizar tabelas dinâmicas automaticamente
Suas tabelas dinâmicas podem ser atualizadas imediatamente e automaticamente quando seus dados de origem mudam?
Absolutamente. Requer o uso de uma macro realmente simples que mostrarei como criar a seguir.
Se você não estiver muito familiarizado com macros e VBA, sugiro conferir minha série de vídeos gratuitos de 3 partes sobre como começar com macros & VBA.
Além disso, se você for novo em tabelas dinâmicas, Tenho uma série para mostrar o que são e como usá-los. Assista ao primeiro vídeo dessa série nas tabelas dinâmicas & Painéis
Para atualizar automaticamente nossas tabelas dinâmicas, vamos escrever uma macro com uma instrução simples. Essa instrução basicamente diz: quando faço uma alteração em minha planilha, atualizo todas as tabelas dinâmicas e conexões de dados. Aqui estão as etapas para criar a macro.
Abra o Editor do Visual Basic.
Você pode fazer isso clicando no botão Visual Basic na guia Desenvolvedor da faixa de opções.
O atalho de teclado para abrir o editor do Visual Basic é Alt + F11.
Se você não vê a guia Desenvolvedor, você pode torná-la visível usando as instruções aqui. Você só precisa fazer isso uma vez e a guia Desenvolvedor estará sempre visível sempre que você abrir o Excel no futuro.
Abra o Módulo de planilha que contém seus dados de origem.
Na janela Project Explorer do editor do Visual Basic, localize a pasta de trabalho que você deseja alterar. Sob essa pasta de trabalho estão listadas as planilhas dentro da pasta de trabalho. Selecione a planilha que contém os dados de origem. Em seguida, clique duas vezes nele.
Se você não vir a janela Explorador de projeto, poderá ativá-la em Exibir menu (atalho de teclado: Ctrl + R).
Adicionar um novo evento para alterações na planilha.
Clicar duas vezes na planilha abre o módulo de código desse objeto. Dentro do módulo de código, queremos criar uma macro de evento. Para fazer isso, escolha Planilha na caixa suspensa Objeto à esquerda.
Isso adicionará um evento Worksheet_SelectionChange ao o módulo, que na verdade não queremos, então vamos excluí-lo em alguns instantes. Antes de fazermos, vamos ao menu suspenso Procedimento à direita e escolha Alterar.
Isso adiciona um novo evento no topo chamado Worksheet_Change. Agora destacaremos e excluiremos o código desnecessário abaixo dele.
A macro de evento Worksheet_Change será executada sempre que uma alteração for feita às células dessa planilha. Podemos adicionar o código VBA ao evento Worksheet_Change para realizar ações quando o usuário edita células.
Nota: O evento SelectionChange adicionado por padrão será executado sempre que o usuário selecionar uma célula na planilha. Como queremos que o código seja executado apenas quando o usuário editar / alterar as células, usamos o evento Change. Confira meu artigo sobre módulos de código VBA & Como executar macros com base em eventos do usuário para saber mais sobre os módulos de planilha e eventos.
Adicione o código VBA para atualizar todas as tabelas dinâmicas.
Em seguida, logo abaixo da linha Worksheet_Change, digite esta instrução:
ThisWorkbook.RefreshAll
O método RefreshAll atualizará todas as tabelas dinâmicas, consultas e conexões de dados na pasta de trabalho. Esta ação é a mesma que se você clicar manualmente no botão Atualizar na guia Dados.
Adicionar esta linha de código ao evento Worksheet_Change atualizará a pasta de trabalho sempre que for feita uma alteração na planilha em que o código é in.
Tabela dinâmica & Dados de origem na mesma planilha
Aleksandrs fez uma ótima pergunta sobre os comentários do vídeo do YouTube. Se sua tabela dinâmica e os dados de origem estiverem na mesma planilha, você precisará adicionar código para desativar os eventos.
A atualização coloca o evento em um loop recursivo e pode acabar travando o Excel. Aqui está o código para evitar isso.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Verificando se a macro está em execução
Uma maneira de verificar se a macro está funcionando é testá-lo. Faça uma alteração nos dados de origem e veja se isso se reflete em sua tabela dinâmica.Se sua alteração não for fácil de detectar porque você tem muitos dados ou por algum outro motivo, há outra maneira de ver se sua macro está disparando.
No editor VB, você pode clicar no botão coluna cinza logo à esquerda de sua macro Worksheet_Change. Isso fará com que um círculo vermelho apareça. Ele também destaca essa linha de código em vermelho.
Isso é chamado de parada ou ponto de interrupção.
O atalho de teclado para ativar / desativar um ponto de interrupção é: F9
Agora, sempre que ocorrer uma ação que acione a macro, o Excel irá para o Editor VB e pausará a macro para que você possa verificar o código. Em nosso caso, essa ação é qualquer alteração feita na planilha.
Você pode então pressionar F8 para percorrer cada linha ou pressione F5 para executar até o fim (ou próximo ponto de interrupção).
Se você fizer uma alteração na planilha e o Excel não puxá-lo para o Editor VB, você saberá que há um problema com a macro não está funcionando. Se for esse o caso, é provável que você não tenha salvado o arquivo como uma pasta de trabalho habilitada para macro (.xlsm) e / ou como macros habilitadas. Pode ser necessário salvar & fechar o arquivo, reabri-lo e habilitar macros.
Para remover o ponto de interrupção que você colocou na macro, basta clicar no círculo vermelho para fazê-lo desaparecer (atalho de teclado: F9).
O atalho de teclado para limpar todos os pontos de interrupção é: Ctrl + Shift + F9
Atualizando tabelas dinâmicas sem um Macro
Uma desvantagem de usar esta macro para atualizar suas tabelas dinâmicas é que qualquer histórico de Desfazer é perdido cada vez que a macro é executada. Em outras palavras, quando você clica no botão Desfazer (ou pressiona Ctrl + Z), o Excel não se lembra da última coisa que você fez, então não pode desfazer. Conseqüentemente, nada acontecerá e sua última alteração não será desfeita.
Há uma alternativa que permite que você mantenha seu histórico de desfazer. No entanto, essa alternativa só atualiza sua tabela dinâmica quando a pasta de trabalho é aberta, não sempre que uma alteração é feita. Aqui está como você pode usar essa opção.
Começando a partir de qualquer célula em sua tabela dinâmica:
- Vá para a guia Analisar na faixa de opções.
- Escolha o botão Opções.
- Vá para a guia Dados na nova janela que é aberta.
- Marque a caixa que diz, “Atualizar dados ao abrir o arquivo.”
Depois de clicar em OK, você poderá obter a seguinte mensagem de aviso se tiver várias tabelas dinâmicas criadas a partir do mesmo intervalo de dados de origem. Basta clicar em OK para passar por isso.
Novamente, apenas a título de comparação, se você usar esta opção, você manterá o histórico de Desfazer, mas só atualizará a tabela dinâmica quando a pasta de trabalho for fechada e reaberta. Se você usar a opção de macro, perderá o histórico de Desfazer, mas a tabela dinâmica será atualizada automaticamente teste sempre que qualquer alteração for feita na pasta de trabalho.
Variações para atualizar tabelas dinâmicas
A macro que examinamos não apenas atualizará suas tabelas dinâmicas, mas também atualizará quaisquer consultas também . Se quiser atualizar apenas as tabelas dinâmicas, você pode substituir o comando “ThisWorkbook.RefreshAll” por este código:
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
Cada tabela dinâmica é conectada a um cache dinâmico subjacente, que está conectado aos dados de origem. O código percorre todos os caches dinâmicos na pasta de trabalho e atualiza cada um. As tabelas dinâmicas do mesmo intervalo de origem podem compartilhar caches dinâmicos, portanto, este método é mais rápido do que percorrer todas as tabelas dinâmicas .
Da mesma forma, digamos que você deseja atualizar apenas uma tabela dinâmica específica. Nesse caso, você pode trocar o código “ThisWorkbook.RefreshAll” pelo código abaixo.
E, finalmente, se você está usando o Power Query e deseja desativar a atualização em segundo plano para que as consultas sejam atualizadas ANTES das tabelas dinâmicas, escrevi um artigo para explicar como fazer isso desabilitando a atualização em segundo plano nas consultas.
Use o Desativar evento em vez disso
Outra opção é usar o evento Worksheet_Deactivate em vez de Worksheet_Change. O evento Worksheet_Deactivate será executado sempre que o usuário sair da planilha e selecionar uma planilha diferente. Isso permite que o usuário faça todas as alterações em os dados de origem, a tabela dinâmica será automaticamente atualizada quando for para qualquer outra planilha, incluindo as planilhas que contêm a tabela dinâmica.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Este código ainda seria colocado no módulo de planilha que contém os dados de origem. Esta é uma boa opção se suas tabelas dinâmicas ou conexões de dados alguns segundos ou mais para atualizar, e você não quer esperar cada vez que uma alteração é feita nos dados de origem.
A única vez que você pode não querer usar isso é se sua tabela dinâmica e fonte os dados estão na mesma folha.Isso geralmente será um caso raro, e algo que geralmente não recomendo.
Graças à sugestão de Ted neste caso.
Economize tempo & Constrangimento
Espero que este artigo ajude você a economizar tempo e torne mais fácil para os usuários de seus arquivos. Também pode ajudar a evitar constrangimento quando você se esquece de atualizar as tabelas dinâmicas antes de enviar relatórios. Acredite eu cometi esse erro mais vezes do que gostaria de admitir … 🙂
