Atualizar tabela dinâmica no VBA

Geralmente, criamos uma tabela dinâmica quando precisamos criar algum tipo de plotagem ou gráfico ou precisamos realizar alguma análise sobre ela. Ao preparar a tabela dinâmica, podemos obter a visão geral e a idéia sobre o que realmente está dentro dos dados. Essa é a melhor maneira de encontrar uma maneira de acessar dados. E toda vez que fazemos alterações nos dados, precisamos atualizar a tabela dinâmica também. Para que também preencha a contagem de dados atualizados. Atualizar qualquer tabela dinâmica é muito fácil, mas e se tivermos 10s de tabelas dinâmicas em um único arquivo do Excel que precisamos atualizar? Portanto, em vez de atualizar todas as tabelas dinâmicas uma por uma, podemos atualizar diretamente todas as tabelas dinâmicas de uma só vez com a ajuda do VBA Refresh Pivot Table.

Como usar a tabela dinâmica de atualização no Excel VBA?

Abaixo estão os exemplos diferentes para usar a Tabela Dinâmica de Atualização no Excel usando o código VBA.

Você pode fazer o download deste modelo do Excel da tabela dinâmica de atualização do VBA aqui - Modelo do Excel da tabela dinâmica de atualização do VBA

Tabela dinâmica de atualização do VBA - exemplo # 1

Para atualizar a tabela dinâmica, primeiro, precisamos criar uma tabela dinâmica. Abaixo, temos os dados do item de linha de 1000s, através do qual criaremos uma tabela dinâmica.

Na captura de tela acima, podemos ver a última linha em 1001 e esses dados contêm informações do cliente em relação à quantidade vendida a eles.

Agora vá para a guia Inserir menu e clique na opção Tabela Dinâmica .

Obteremos uma caixa Criar tabela dinâmica. A partir daí, primeiro, selecione o intervalo da tabela que queremos incluir em uma tabela dinâmica. Em seguida, selecione qualquer local na planilha atual da tabela dinâmica. Também podemos escolher Nova planilha.

Uma vez feito, clique em Ok.

Obteremos a tabela dinâmica criada. Agora arraste os campos obrigatórios para diferentes áreas para obter a tabela dinâmica real. Aqui, arrastamos o nome do cliente para ROWS e a quantidade para COLUMNS, conforme mostrado abaixo.

Isso conclui a criação de uma tabela dinâmica. Mas precisamos atualizar a tabela dinâmica depois de alterar qualquer coisa em Dados. Isso pode ser feito simplesmente com a opção de atualização na lista do menu suspenso Clique com o botão direito do mouse, conforme mostrado abaixo.

Mas o mesmo processo também pode ser automatizado através do VBA. Para isso, precisaríamos de um módulo. Então,

Etapa 1: Vá para a guia do menu Inserir e selecione a opção Módulo na lista suspensa, como mostrado abaixo.

Etapa 2: no recém-aberto módulo, escreva a subcategoria VBA Pivot Refresh ou podemos escolher qualquer nome conforme nossa escolha.

Código:

 Sub Pivot_Refresh2 () End Sub

Etapa 3: primeiro, defina uma variável como PivotCache, como mostrado abaixo. O PivotCache usa o cache do Pivot e não os dados usados ​​para criar a tabela dinâmica.

Código:

 Tabela Sub Dim Pivot_Refresh2 () como PivotCache End Sub

Etapa 4: Agora usaremos o loop For-Each . Portanto, abra um loop For-Each, como mostrado abaixo.

Código:

 Sub Pivot_Refresh2 () Tabela Dim como PivotCache para cada próxima tabela End Sub

Etapa 5: Agora, dentro do loop For-End, escreveremos a condição em que selecionaremos a planilha aberta no momento, que possui uma tabela dinâmica com o PivotCache .

Código:

 Sub Pivot_Refresh2 () Tabela Dim como PivotCache Para cada tabela em ThisWorkbook.PivotCaches Próxima tabela End Sub

Etapa 6: Agora, usaremos o comando Atualizar, atribuindo a variável Tabela definida.

Código:

 Sub Pivot_Refresh2 () Tabela Dim como PivotCache para cada tabela em ThisWorkbook.PivotCaches Table.Refresh Próxima tabela End Sub

Etapa 7: isso completa o código, agora compile o código para arquivar um erro pressionando a tecla funcional F8. Agora, para testar, se o código escrito não funciona, alteramos a quantidade de Customer14 para 69 .

Etapa 8: agora execute o código. Veremos que a contagem total em relação ao Cliente14 é atualizada para 2183, destacada em amarelo.

Tabela dinâmica de atualização do VBA - exemplo # 2

Há outra maneira de atualizar a tabela dinâmica por meio do VBA. Antes de avançarmos, podemos considerar a alteração do nome de uma tabela dinâmica ou também podemos usar o nome padrão. Vamos tentar dar um novo nome à tabela dinâmica. Para isso, selecione a tabela dinâmica e vá para a guia do menu Analisar, como mostrado abaixo.

Na primeira seção do Nome da tabela dinâmica, podemos ver o nome padrão como Tabela dinâmica1 .

Agora vamos mudar esse nome. Considere escrever o novo nome como Dados do cliente, como mostrado abaixo.

Etapa 1: depois disso, abra um novo módulo e escreva a subcategoria de atualização do VBA, como mostrado abaixo.

Código:

 Sub Pivot_Refresh3 () End Sub

Etapa 2: Agora defina uma variável como Tabela Dinâmica . Aqui, com a Tabela Dinâmica, consideraremos os dados completos da fonte.

Código:

 Sub Pivot_Refresh3 () Tabela Dim como tabela dinâmica End Sub

Etapa 3: Agora use Definir com variável definida e selecione a planilha que está aberta no momento.

Código:

 Sub Pivot_Refresh3 () Dim Table As PivotTable Set Table = ActiveSheet. End Sub

Etapa 4: selecione o nome da tabela dinâmica que queremos atualizar junto com o tipo de dados variável que usamos como tabela dinâmica .

Código:

 Sub Pivot_Refresh3 () Tabela Dim como tabela dinâmica Definir tabela = ActiveSheet.PivotTables ("Dados do cliente") End Sub

Etapa 5: Por fim, use a variável com a função RefreshTable para executá-la.

Código:

 Sub Pivot_Refresh3 () Dim Table As PivotTable Set Table = ActiveSheet.PivotTables ("Customer Data") Table.RefreshTable End Sub

Etapa 6: Vamos fazer mais algumas alterações nos dados, para fornecer os visuais reais do código aplicado.

Alteramos a contagem de Customer2 como 56 . Agora, se executarmos o código, na tabela dinâmica de Nome do Cliente, deve haver as alterações na soma da quantidade para o Cliente2 .

Etapa 7: Agora vá para a janela VBA e compile o código. Se nenhum erro for encontrado, execute-o clicando no botão play, que fica abaixo da barra de menus, como mostrado abaixo. Notaremos que uma soma da quantidade vendida para o Customer2 agora está subindo para 1724, fazendo as alterações na tabela de origem.

Com isso, podemos incluir mais de um dado de origem e criar uma tabela dinâmica diferente. E automatizar essas tabelas dinâmicas também é fácil, pois precisamos incluir apenas o nome e a planilha da tabela dinâmica em que a tabela estava localizada.

Profissionais da tabela dinâmica de atualização do VBA

  • Leva muito menos tempo atualizando a tabela dinâmica pelo código VBA.
  • É muito fácil de implementar.

Coisas para lembrar

  • Podemos adicionar várias tabelas de fontes de dados e automatizá-las pelo código VBA.
  • Para ver as alterações, mantenha a janela do VBA e a planilha do Excel paralelas uma à outra.
  • É melhor nomear cada tabela dinâmica se você estiver lidando e manipulando várias fontes de dados e tabelas dinâmicas.
  • O código com o nome da tabela dinâmica é fácil de entender, rastrear e localizar.

Artigos recomendados

Este é um guia para a Tabela Dinâmica de Atualização do VBA. Aqui discutimos como usar a Tabela Dinâmica de Atualização do VBA do Excel, juntamente com exemplos práticos e modelo de excel para download. Você também pode consultar nossos outros artigos sugeridos -

  1. VBA FileSystemObject (FSO)
  2. Função Excel de Substring
  3. Comprimento da string do VBA
  4. Fórmula ISNUMBER do Excel

Categoria:

VBA