Atualizar todas as tabelas dinâmicas no Excel com VBA

Congresso nacional de Excel Online - Inscreva-se
Congresso nacional de Excel Online – Inscreva-se
16 de fevereiro de 2015
Formatar tabela dinâmica Excel automaticamente
Formatar tabela dinâmica Excel automaticamente
10 de março de 2015

Objetivo: Disponibilizar uma forma para atualizar todas as tabelas dinâmicas de um arquivo Excel utilizando VBA.

Atualizar todas as tabelas dinâmicas Excel

Esta é uma situação que tive na empresa outro dia, principalmente quando houverem muitas tabelas dinâmicas.

O Excel normalmente atualiza todos os seus dados e também as tabelas dinâmicas clicando no botão Atualizar Todos na guia Dados->Atualizar Tudo, porém já vi situações aonde há muitas tabelas  dinâmicas, principalmente com conexão a banco de dados aonde as tabelas dinâmicas por algum motivo não atualizam todas, sendo então necessário atualizar todas manualmente.

Para isso você pode utilizar o seguinte código fonte em VBA:

Sub RefreshPivotTables()
  'Objeto de tabela dinâmica
  Dim pivotTable As pivotTable
 
  'Loop por todos os objetos da planilha
  For Each plan In ActiveWorkbook.Sheets
    For Each pivotTable In plan.PivotTables
        pivotTable.RefreshTable
    Next
  Next
End Sub

Veja neste outro artigo como aplicar este e outros códigos VBA que você buscar na internet: o: http://guiadoexcel.com.br/habilitando-a-guia-desenvolvedor-e-copiando-procedimentos-vba-sub-da-internet

Abraço

Marcos Rieper

24 Comments

  1. Linelson disse:

    Li hoje em um site gringo um código semelhante:

    Private Sub Worksheet_Deactivate()

    Dim pt As PivotTable
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

    For Each pt In ws.PivotTables

    pt.RefreshTable

    Next pt

    Next ws

    End Sub

  2. Jonas Bastos disse:

    melhorei o código, pois pode haver possibilidade de erro ao atualizar as Td.

    adicionei tbm para que limpe os filtros das td.

    Private Sub tabela_dinamica()

    Dim pt As PivotTable
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
    On Error Resume Next
    pt.RefreshTable
    pt.ClearAllFilters ‘limpa os filtros
    Next pt
    Next ws
    On Error GoTo 0

    End Sub

  3. Danilo disse:

    Caso tenha uma das planilhas que seja um gráfico, por exemplo, o script que sugeriu dará erro. Segue uma pequena sugestão:

    ‘Objeto de tabela dinâmica
    Dim pivotTable As pivotTable
    ‘Loop por todos os objetos da planilha
    For Each plan In ActiveWorkbook.Sheets
    If (plan.Type = xlWorksheet) Then
    For Each pivotTable In plan.PivotTables
    pivotTable.RefreshTable
    Next
    End If
    Next

  4. Amir Andrade disse:

    Marcos, Sou um grande fã do seu trabalho. Diariamente tenho de atualizar várias tabelas dinamicas e utilizo este código, mas enfrento um problema um tanto irritante: Ao atualizar, o excel gera uma msgbox perguntanto se pode sobreescrever o cabo aonde a tabela dinamica vai expandir (Deixo o campo limpo, branco e sem linhas de grade) Existe uma forma de responder esta caixa de mensagem automaticamente ou evitar que ela apareça? Agradeço.

    • Marcos Rieper disse:

      Olá Amir,

      Tente ao invés de limpar estes dados excluir as linhas inteiras abaixo e as colunas á direita. Ás vezes pode ser formatação.

      E muito obrigado por acompanhar nosso trabalho, fico muito feliz com isso.

      Abraço
      Marcos Rieper

  5. Luiz Aguiar disse:

    Marcos,

    Boa tarde, tudo bem? Parabens pelo site e pelo contéudo. Muito bom mesmo. Queria tirar uma dúvida, trabalho com um arquivo que fica disponibilizado no Evernote, porém quando coloco a macro do botão de “Atualizar Tudo” ele dá um erro que não localizou o arquivo em questão.

    Existe alguma cautela em utilizar arquivos vinculados no Evernote para fazer macros e para atualizar posteriormente?

    Obrigado e abraço

  6. Adriana disse:

    Gostaria de saber onde deve ser colocado o código, na Plan onde estão os dados ou em um módulo?, e a atualização da tabela dinâmica se dará todas as vezes que salvar o arquivo?

    • Marcos Rieper disse:

      Olá Adriana,

      Você pode colocar no evento do Excel, pode ser no clicar de uma planilha ou no fechar da pasta de trabalho, como desejar.
      Pode ser colocado em um módulo e chamado aonde desejar, mas coloque como Public neste caso, ou pode também aplicar diretamente no evento, caso somente vá usar naquela situação.

      At.
      Marcos Rieper

  7. Breno Pereira disse:

    Boa tarde Marcos,

    Minha segunda do comando, tem apenas uma Tabela Dinâmica.
    Estou rodando o comando abaixo, porém ele aparece Calculando umas de 0 a 100% umas 4 vezes, antes de concluir.

    Sabe o que posso fazer para que ele calcule apenas 1 vez?

  8. Eder Lima disse:

    Bom dia,
    Marcos, tem possibilidades em rodar esta macro com as planilhas protegidas?

    Abraços.

  9. ALEX disse:

    O código também funciona em tabelas dinâmicas em planilhas ocultas?

  10. Rodrigo M disse:

    Marcos,

    Uma pergunta:

    Ao dar refresh na tabela, está mudando a configuração de soma para contagem. Como poderia ajustar?

  11. Eliane Colvet disse:

    Olá Marcos
    Tenho uma planilha com 5 abas, precisa dividir porque em cada aba tenho cerca de 700.000 linhas.
    Os valores de “label” se repetem nas 5 abas, porém cada uma delas com dados diferentes em cada um.
    Preciso montar uma tabela dinâmica que cheque os dados nas 5 abas, para somatórias, etc… É possível?
    Grata
    Eliane

  12. Raphael Norberto disse:

    Olá Marcos, sempre que preciso de ajuda venho a seu blog.
    Parabéns pelo trabalho, realmente é muito bom.
    Tenho uma duvida que não encontrei solução ainda.
    Tenho um gráfico dinâmico que tem como base a tabela. Esta eu consigo atualizar automaticamente, mas o gráfico não.
    O problema se dá, pois para que eu complete a atualização é necessário que eu clique na aba da tabela, há alguma forma de automatizar e o gráfico atualizar também automaticamente?

  13. Marcos André disse:

    Olá, sou novo no que diz respeito ao excel avançado, estou tentanto criar um projeto para cadastrar os funcionários de um órgão onde trabalho é coisa básica mesmo, são dados cadastrais tipo nome, endereço, idt, telefone… e por aí vai, só não estou conseguindo criar os códigos para atualizar os dados desta planilha quando eu altero os registros da mesma, alguém por poderia me orientação de como fazer este código, ficarei muito agradecido . E desde logo quero aproveita a oportunidade para agradecer também ao administrador do site pelo trabalho.

    Att,

    Marcos André

  14. Carlos Pereira disse:

    Muito bom me ajudou muito !

    Mas gostaria de aplicar esse código da seguinte forma, tenho um relatório com 20 abas dinâmicas onde atualizo manualmente e a minha base de dados fica em outro arquivo pois tem mais de 200 mil linhas, como posso utilizar o código ?

  15. José de Castro disse:

    Olá Marcos

    Tenho uma preocupação: Sempre que gravo uma macro com o código para actualizar automaticamente os dados de uma Tabela Dinâmica, pergunta o seguinte: “Atenção! Algumas partes do seu documento poderão incluir informações pessoais que não poderão ser removidas pelo inspector do Documentos, quer clicar OK ou Cancel?”. Se clicar “OK” o ficheiro Excel-Macro é gravado, mas se fechá-lo e voltar a abri-lo e de seguida lançar novos dados a Tabela Dinâmica deixa de reagir para actualizar. Qual é a sua diga para ultrapassar este impasse?

    Qual é o sigificado deste código seguinte criado na minha macro e se abrange todos os dados que devem ser actualizados na Tabela Dinâmica?

    Sub Macro1()

    ‘ Macro1 Macro


    Range(“E5”).Select
    ActiveSheet.PivotTables(“Tabela dinâmica1”).PivotCache.Refresh
    Range(“G22”).Select
    End Sub

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.