Atualmente, podemos alterar ou atualizar dinamicamente as tabelas dinâmicas usando tabelas do Excel ou intervalos nomeados dinâmicos. Mas essas técnicas não são infalíveis. Como você ainda terá que atualizar a tabela dinâmica manualmente. Se você tiver grandes dados que contenham milhares de linhas e colunas, as tabelas do Excel não o ajudarão muito. Em vez disso, isso tornará seu arquivo pesado. Portanto, a única maneira que resta é o VBA.
Neste artigo, aprenderemos como podemos fazer nossa tabela dinâmica alterar automaticamente a fonte de dados. Em outras palavras, iremos automatizar o processo manual de alteração da fonte de dados para incluir dinamicamente novas linhas e colunas adicionadas às tabelas de origem e refletir a mudança na tabela dinâmica instantaneamente.
Escreva o código na planilha de dados de origem
Como queremos que isso seja totalmente automático, usaremos módulos de folha para escrever código em vez de um módulo principal. Isso nos permitirá usar eventos de planilha.
Se os dados de origem e as tabelas dinâmicas estiverem em planilhas diferentes, escreveremos o código VBA para alterar a origem de dados da tabela dinâmica no objeto de planilha que contém os dados de origem (não aquele que contém a tabela dinâmica).
Pressione CTRL + F11 para abrir o editor VB. Agora vá para o explorador de projetos e encontre a planilha que contém os dados de origem. Clique duas vezes nele.
Uma nova área de codificação será aberta. Você pode não ver nenhuma mudança, mas agora você tem acesso aos eventos da planilha.
Clique no menu suspenso à esquerda e selecione a planilha. No menu suspenso à esquerda, selecione desativar. Você verá um sub em branco escrito no nome da área de código worksheet_deativate. Nosso código para alterar dinamicamente os dados de origem e atualizar a tabela dinâmica estará neste bloco de código. Este código será executado sempre que você mudar da planilha de dados para qualquer outra planilha. Você pode ler sobre todos os eventos da planilha aqui.
Agora estamos prontos para implementar o código.
Código-fonte para atualizar a tabela dinâmica dinamicamente com o novo intervalo
Para explicar como funciona, tenho uma apostila. Este livro contém duas planilhas. A Planilha1 contém os dados de origem que podem ser alterados. Folha2 contém a tabela dinâmica que depende dos dados de origem da folha2.
Agora eu escrevi este código na área de codificação da folha1. Estou usando o evento Worksheet_Deactivate, para que este código seja executado para atualizar a tabela dinâmica sempre que mudarmos da planilha de dados de origem.
Sub Private Worksheet_Deactivate () Dim pt As PivotTable Dim pc As PivotCache Dim source_data As Range lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft). Conjunto de colunas source_data = Range (Cells (1, 1), Cells (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivotCache pc End Sub
Se você tiver uma pasta de trabalho semelhante, poderá copiar esses dados diretamente. Expliquei que esse código funciona a seguir.
Você pode ver o efeito deste código no gif abaixo.
Como esse código altera automaticamente os dados de origem e atualiza as tabelas dinâmicas?
Em primeiro lugar, usamos um evento worksheet_deactivate. Este evento é disparado apenas quando a planilha que contém o código é trocada ou desativada. Portanto, é assim que o código é executado automaticamente.
Agora, para alterar os dados de origem da tabela dinâmica, alteramos os dados no cache dinâmico.
Uma tabela dinâmica é criada usando o cache dinâmico. O cache dinâmico contém os dados de origem antigos até que a tabela dinâmica não seja atualizada manualmente ou o intervalo de dados de origem seja alterado manualmente.
Criamos referências de tabelas dinâmicas com o nome pt, cache dinâmico denominado pc e um intervalo denominado source_data. Os dados de origem conterão todos os dados.
Para obter dinamicamente toda a tabela como intervalo de dados, determinamos a última linha e a última coluna.
lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row
lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column
Usando esses dois números, definimos source_data. Estamos certos de que o intervalo de dados de origem sempre iniciará em A1.
Definir source_data = Range (Células (1, 1), Células (lstrow, lstcol))
Agora temos os dados de origem que são dinâmicos. Precisamos apenas usá-lo na tabela dinâmica.
Armazenamos esses dados no cache dinâmico, pois sabemos que o cache dinâmico armazena todos os dados.
Definir pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Em seguida, definimos a tabela dinâmica que queremos atualizar. Como queremos atualizar a Tabela Dinâmica1 (nome da tabela dinâmica. Você pode verificar o nome da tabela dinâmica na guia analisar enquanto seleciona a tabela dinâmica.) Na planilha1, definimos pt conforme mostrado abaixo.
Definir pt = Sheet2.PivotTables ("PivotTable1")
Agora, simplesmente usamos esse cache dinâmico para atualizar a tabela dinâmica. Usamos o método changePivotCache do objeto pt.
pt.ChangePivotCache pc
E temos nossa tabela dinâmica automatizada. Isso atualizará automaticamente sua tabela dinâmica. Se você tiver várias tabelas com a mesma fonte de dados, basta usar o mesmo cache em cada objeto de tabela dinâmica.
Então, sim pessoal, é assim que você pode alterar dinamicamente o intervalo da fonte de dados no Excel. Espero ter sido suficientemente explicativo. Se você tiver alguma dúvida sobre este artigo, deixe-me saber na seção de comentários abaixo.
Como atualizar automaticamente tabelas dinâmicas usando VBA: Para atualizar automaticamente suas tabelas dinâmicas, você pode usar eventos VBA. Use esta linha simples de código para atualizar sua tabela dinâmica automaticamente. Você pode usar um dos três métodos de atualização automática de tabelas dinâmicas.
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.