Como todos sabemos, sempre que fazemos alterações nos dados de origem de uma tabela dinâmica, isso não reflete imediatamente na tabela dinâmica. Precisamos atualizar as tabelas dinâmicas para ver as mudanças. E se você enviar um arquivo atualizado sem atualizar as tabelas dinâmicas, poderá ficar constrangido.
Portanto, neste artigo, aprenderemos como atualizar automaticamente uma tabela dinâmica usando o VBA. Dessa forma é mais fácil do que você imaginou.
Esta é a sintaxe simples para atualizar automaticamente as tabelas dinâmicas na pasta de trabalho.
'Código na fonte de dados do objeto Private Sub Worksheet_Deactivate () sheetname_of_pivot_table.PivotTables ("pivot_table_name"). PivotCache.Refresh End Sub
O que são Pivot Caches?
Cada tabela dinâmica armazena os dados no cache dinâmico. É por isso que o pivot é capaz de mostrar dados anteriores. Quando atualizamos as tabelas dinâmicas, ele atualiza o cache com novos dados de origem para refletir as alterações na tabela dinâmica.
Portanto, precisamos apenas de uma macro para atualizar o cache das tabelas dinâmicas. Faremos isso usando um evento de planilha para que não tenhamos que executar a macro manualmente.
Onde codificar para atualizar automaticamente as tabelas dinâmicas?
Se seus dados de origem e tabelas dinâmicas estiverem em planilhas diferentes, o código do VBA deve ir na planilha de dados de origem.
Aqui, usaremos o evento Worksheet_SelectionChange. Isso fará com que o código seja executado sempre que mudarmos da planilha de dados de origem para outra planilha. Explicarei mais tarde porque usei este evento.
Aqui, tenho dados de origem na planilha2 e tabelas dinâmicas na planilha1.
Abra o VBE usando as teclas CTRL + F11. No explorador de projetos, você pode ver três objetos, Planilha1, Planilha2 e a pasta de trabalho.
Como a Planilha2 contém os dados de origem, clique duas vezes no objeto da planilha2.
Agora você pode ver dois menus suspensos no topo da área de código. Na primeira lista suspensa, selecione a planilha. E no segundo menu suspenso, selecione Desativar. Isso irá inserir um subnome vazio Worksheet_Deactivate. Nosso código será escrito neste sub. Quaisquer linhas escritas neste sub, são executadas assim que o usuário muda desta planilha para qualquer outra planilha.
Na folha 1, tenho duas tabelas dinâmicas. Quero atualizar apenas uma tabela dinâmica. Para isso, preciso saber o nome da tabela dinâmica. Para saber o nome de qualquer tabela dinâmica, selecione qualquer célula dessa tabela dinâmica e vá para a guia de análise da tabela dinâmica. No lado esquerdo, você verá o nome da tabela dinâmica. Você também pode alterar o nome da tabela dinâmica aqui.
Agora que sabemos o nome da tabela dinâmica, podemos escrever uma linha simples para atualizá-la.
Private Sub Worksheet_Deactivate () Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
E está feito.
Agora, sempre que você alternar dos dados de origem, este código vba será executado para atualizar a tabela dinâmica1. Como você pode ver no gif abaixo.
Como atualizar todas as tabelas dinâmicas da pasta de trabalho?
No exemplo acima, queríamos apenas atualizar uma tabela dinâmica específica. Mas se você deseja atualizar todas as tabelas dinâmicas em uma pasta de trabalho, você só precisa fazer pequenas alterações em seu código.
Private Sub Worksheet_Deactivate () 'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh para cada pc nesteLivro.PivotCaches pc.Refresh Next pc End Sub
Neste código, estamos usando um loop For para percorrer cada cache de pivô na pasta de trabalho. O objeto ThisWorkbook contém todos os caches dinâmicos. Para acessá-los, usamos ThisWorkbook.PivotCaches.
Por que usar o evento Worksheet_Deactivate?
Se você quiser atualizar a tabela dinâmica assim que qualquer alteração for feita nos dados de origem, você deve usar o evento Worksheet_Change. Mas eu não recomendo. Isso fará com que sua pasta de trabalho execute o código toda vez que você fizer qualquer alteração na planilha. Você pode ter que fazer centenas de mudanças antes de ver o resultado. Mas o Excel atualizará a tabela dinâmica a cada mudança. Isso levará ao desperdício de tempo e recursos de processamento. Portanto, se você tiver tabelas dinâmicas e dados em planilhas diferentes, é melhor usar o evento de desativação da planilha. Ele permite que você finalize seu trabalho. Depois de alternar para as planilhas de tabela dinâmica para ver as alterações, isso corrige as alterações.
Se você tiver tabelas dinâmicas e dados de origem na mesma planilha e quiser que as tabelas dinâmicas se atualizem automaticamente, você pode usar o evento Worksheet_Change.
Private Sub Worksheet_Change (ByVal Target As Range) Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
Como atualizar tudo nas pastas de trabalho quando uma alteração é feita nos dados de origem?
Se você deseja atualizar tudo em uma pasta de trabalho (gráficos, tabelas dinâmicas, fórmulas, etc.), você pode usar o comando ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change (ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Observe que este código não altera a fonte de dados. Portanto, se você adicionar dados abaixo dos dados de origem, esse código não incluirá esses dados automaticamente. Você pode usar tabelas do Excel para armazenar dados de origem. Se você não quiser usar tabelas, podemos usar o VBA para incluir novos dados também. Vamos aprender no próximo tutorial.
Então, sim, cara, é assim que você pode atualizar automaticamente as tabelas dinâmicas no Excel. Espero ter sido suficientemente explicativo e este artigo serviu bem para você. Se você tiver alguma dúvida sobre este tópico, pode perguntar-me na seção de comentários abaixo.
Como atualizar dinamicamente o intervalo da fonte de dados da tabela dinâmica no Excel: Para alterar dinamicamente o intervalo de dados de origem das tabelas dinâmicas, usamos caches dinâmicos. Essas poucas linhas podem atualizar dinamicamente qualquer tabela dinâmica, alterando o intervalo de dados de origem. No VBA, use objetos de tabelas dinâmicas conforme mostrado abaixo …
Executar macro se houver alguma alteração feita na folha no intervalo especificado: Em suas práticas de VBA, você teria a necessidade de executar macros quando um determinado intervalo ou célula fosse alterado. Nesse caso, para executar macros quando uma alteração é feita em um intervalo de destino, usamos o evento de alteração.
Executar macro quando qualquer alteração for feita na planilha | Portanto, para executar sua macro sempre que a planilha for atualizada, usamos os Eventos de Planilha do VBA.
Código VBA mais simples para destacar a linha e a coluna atuais usando | Use este pequeno trecho de VBA para destacar a linha e coluna atuais da planilha.
Os eventos de planilha no Excel VBA | Os eventos de planilha são realmente úteis quando você deseja que suas macros sejam executadas quando um evento especificado ocorrer na planilha.
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
A função VLOOKUP no Excel | Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
CONT.SE no Excel 2016 | Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar um valor específico. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.