Como atualizar dinamicamente todas as fontes de dados das tabelas dinâmicas no Excel

Em um artigo anterior, aprendemos como você pode alterar e atualizar dinamicamente tabelas dinâmicas individuais reduzindo ou expandindo fontes de dados.

Neste artigo, iremos aprenda como podemos fazer com que todas as tabelas dinâmicas em uma pasta de trabalho alterem automaticamente a fonte de dados. Em outras palavras, em vez de alterar uma tabela dinâmica de cada vez, tentaremos alterar a fonte de dados de todas as tabelas dinâmicas na pasta de trabalho para incluir dinamicamente novas linhas e colunas adicionadas às tabelas de origem e refletir a alteração nas tabelas dinâmicas instantaneamente.

Escrever código na planilha de dados fonte

Como queremos que isso seja totalmente automático, usaremos módulos de planilha 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 fonte de dados da tabela dinâmica no objeto de planilha que contém os dados de origem ( não que contenha tabelas dinâmicas).

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 entrará 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 dinamicamente todas as tabelas dinâmicas na pasta de trabalho com o novo intervalo

Para explicar como funciona, eu tenho uma pasta de trabalho. Este livro contém três planilhas. Folha1 contém os dados de origem que podem ser alterados. Planilha2 e Planilha3 contêm tabelas dinâmicas que dependem dos dados de origem da planilha2.

Agora, escrevi este código na área de codificação da planilha1. Estou usando o evento Worksheet_Deactivate, para que este código seja executado para atualizar o tabela dinâmica sempre que mudarmos da planilha de dados de origem.

Se você tiver uma pasta de trabalho semelhante, poderá copiar esses dados diretamente. Expliquei que este código funciona abaixo para que você possa modificá-lo de acordo com suas necessidades.

Você pode ver o efeito desse código em gif abaixo.

Como esse código muda automaticamente dados de origem e tabelas dinâmicas de atualização?

Em primeiro lugar, usamos um evento worksheet_deactivate. Este evento dispara apenas quando a planilha que contém o código é trocada ou desativada. Portanto, é assim que o código é executado automaticamente.

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 (xlToL eft) .Column

Usando esses dois números, definimos source_data. Estamos certos de que o intervalo de dados de origem sempre iniciará em A1. Você pode definir sua própria referência de célula inicial.

Definir source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))

Agora temos os dados de origem que são dinâmico. Só precisamos usá-lo na tabela dinâmica.

Como não sabemos quantas tabelas dinâmicas uma pasta de trabalho conterá por vez, faremos um loop em cada planilha e nas tabelas dinâmicas de cada planilha. que nenhuma tabela dinâmica foi deixada. Para isso, usamos loops for aninhados.

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Próximo pt

Próximo ws

O primeiro loop percorre cada planilha. O segundo loop itera sobre cada tabela dinâmica em uma planilha.

As tabelas dinâmicas são atribuídas para a variável pt. Usamos o método ChangePivotCache do objeto pt. Criamos dinamicamente um cache dinâmico usando o método ThisWorkbook.PivotCaches.Create

. Este método usa duas variáveis SourceType e SourceData. Como tipo de fonte declaramos xlDatabase e como SourceData, passamos o intervalo source_data que calculamos anteriormente.

E isso i sentar. Temos nossas tabelas dinâmicas automatizadas. Isso atualizará automaticamente todas as tabelas dinâmicas na pasta de trabalho.

Sim, pessoal, é assim que você pode alterar dinamicamente os intervalos de fonte de dados de todas as tabelas dinâmicas em uma pasta de trabalho 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 dinamicamente o intervalo da fonte de dados da tabela dinâmica no Excel: Para alterar dinamicamente o intervalo da fonte de dados 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.

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 qualquer um dos três métodos de atualização automática de tabelas dinâmicas.

Executar macro se houver alguma alteração feita na planilha em um intervalo especificado: Em suas práticas de VBA, você teria a necessidade de executar macros em um determinado intervalo ou mudanças celulares. 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 específico 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.

COUNTIF 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 a somar valores em condições específicas.