Em um artigo anterior, aprendemos como você pode alterar e atualizar dinamicamente as tabelas dinâmicas individuais reduzindo ou expandindo as fontes de dados.
Neste artigo, aprenderemos 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 por 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.
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 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 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 dinamicamente todas as tabelas dinâmicas na pasta de trabalho com novo intervalo
Para explicar como funciona, tenho uma apostila. Este livro contém três planilhas. A Planilha1 contém os dados de origem que podem ser alterados. A Planilha2 e a Planilha3 contêm tabelas dinâmicas que dependem dos dados de origem da Planilha2.
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.
Private Sub Worksheet_Deactivate () Dim source_data As Range 'Determinando a última linha e o número da coluna lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column 'Definindo o novo intervalo Set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))' Código para percorrer cada planilha e tabela dinâmica Para Cada ws NesteWorkbook.Worksheets Para Cada pt Em ws.PivotTables pt. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Next pt Next ws End Sub
Se você tiver uma pasta de trabalho semelhante, poderá copiar esses dados diretamente. Expliquei que esse código funciona a seguir para que você possa modificá-lo de acordo com suas necessidades.
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.
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. Você pode definir sua própria referência de célula inicial.
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.
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. Para que nenhuma tabela dinâmica seja deixada. Para isso, usamos loops for aninhados.
Para cada ws neste livro de trabalho.Folhas de trabalho
Para cada ponto em ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
SourceType: = xlDatabase, _
SourceData: = source_data)
Próximo pt
Próximo ws
O primeiro loop percorre cada folha. O segundo loop itera sobre cada tabela dinâmica em uma planilha.
As tabelas dinâmicas são atribuídas a variáveis pt. Usamos o método ChangePivotCache do objeto pt. Criamos dinamicamente um cache dinâmico usando ThisWorkbook.PivotCaches.Create
Método. 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. Temos nossas tabelas dinâmicas automatizadas. Isso atualizará automaticamente todas as tabelas dinâmicas na pasta de trabalho.
Então, 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 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.
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.
