Tabela Dinâmica VBA

Muitos relatórios corporativos se resumem a tabelas dinâmicas que precisam ser criadas e atualizadas.

O VBA auxilia na automação deste processo.

Criando uma Tabela Dinâmica no VBA

Utilizaremos como base a tabela presente na planilha da imagem a seguir:

Observe que o nome das colunas está presente na linha 1 e os dados estão completos até a célula "D9".

Primeiramente determinamos os dados nos quais se irá trabalhar:

Dim TabRange As Range

Set TabRange = Cells(1, 1).CurrentRegion 'Associar intervalo com dados à variável

E associamos esses dados a uma fonte acessível a Tabela Dinâmica. Para tal utilizaremos uma variável de cache (de acesso rápido na memória) própria para isso.

Dim TabCache As PivotCache 'Tipo de dado para Tabela Dinâmica

'Define a fonte de dados da Tabela Dinâmica (que ficará em cache)
Set TabCache = ActiveWorkbook.PivotCaches _
.Create(SourceType:=xlDatabase, SourceData:=TabRange)

A utilização do _ (underline) não interfere na execução, mas sim na visualização do código, pois permite a quebra de linha ao utilizarmos consecutivos métodos e propriedades.

'Ambas as linhas abaixo partindo de Set são iguais e realizam a mesma ação
Set TabCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=TabRange)
Set TabCache = ActiveWorkbook.PivotCaches _
    .Create(SourceType:=xlDatabase, SourceData:=TabRange)

Em seguida determinamos aonde a Tabela Dinâmica irá ficar. No nosso caso, iremos construir em uma aba dedicada.

ActiveWorkbook.Sheets.Add 'Adiciona uma nova planilha, que se torna ativa
ActiveSheet.Name = "TabelaDinamica"

Inserimos a Tabela Dinâmica propriamente dita:

Dim TabDin As PivotTable 'Tipo para Tabela Dinâmica

'Inserir a Tabela Dinâmica na planilha
Set TabDin = TabCache.CreatePivotTable _
(TableDestination:=Cells(1, 1), TableName:="TabelaDinamica1")

A Tabela Dinâmica já está pronta para uso. Para manipular seus campos devemos chamá-los com PivotFields, determinando sua orientação (linha, coluna ou valores agregados) com a propriedade .Orientation.

    'Adiciona o item aos títulos da linha
    TabDin.PivotFields("Ano").Orientation = xlRowField

    'Adiciona o item aos títulos da coluna
    TabDin.PivotFields("Região").Orientation = xlColumnField

    'Inserir dados agregados de Quantidade pela soma
    With TabDin.PivotFields("Quantidade")
        .Orientation = xlDataField
        .Function = xlSum
    End With

With é utilizado para associar ou chamar diferentes métodos e propriedades de um objeto ao mesmo tempo (necessário para algumas operações).

Sem With
  Cells(1,1).Value=-10
  Cells(1,1).Interior.Color = RGB(255, 0, 0)
Com With
  With Cells(1,1)
      .Value=-10
      .Interior.Color = RGB(255, 0, 0)
  End With

Juntando todos os passos teríamos:

Sub criarTabelaDinamica()

    Dim TabRange As Range
    Dim TabCache As PivotCache 'Tipo dados para Tabela Dinâmica
    Dim TabDin As PivotTable 'Tipo para Tabela Dinâmica

    Set TabRange = Cells(1, 1).CurrentRegion 'Associar intervalo com dados à variável

    'Define a fonte de dados da Tabela Dinâmica (que ficará em cache)
    Set TabCache = ActiveWorkbook.PivotCaches _
    .Create(SourceType:=xlDatabase, SourceData:=TabRange)

    ActiveWorkbook.Sheets.Add 'Adiciona uma nova planilha, que se torna ativa
    ActiveSheet.Name = "TabelaDinamica"

    'Inserir a Tabela Dinâmica na planilha
    Set TabDin = TabCache.CreatePivotTable _
    (TableDestination:=Cells(1, 1), TableName:="TabelaDinamica1")

    'Adiciona o item aos títulos da linha
    TabDin.PivotFields("Ano").Orientation = xlRowField

    'Adiciona o item aos títulos da coluna
    TabDin.PivotFields("Região").Orientation = xlColumnField

    'Inserir dados agregados de Quantidade pela soma
    With TabDin.PivotFields("Quantidade")
        .Orientation = xlDataField
        .Function = xlSum
    End With

End Sub

Atualizar Tabela Dinâmica

Para atualizarmos uma Tabela Dinâmica onde a fonte de dados apenas sofreu modificações de valores (e não alterações de tamanho de intervalo) pode-se utilizar o método .Refresh

Sub atualizarValores()

    'Atualizar somente uma Tabela Dinâmica
    Worksheets("TabelaDinamica").PivotTables("TabelaDinamica1").PivotCache.Refresh

    'Atualizar todas as Tabelas Dinâmicas do documento
    ActiveWorkbook.RefreshAll

End Sub

É preciso que exista a Tabela Dinâmica para que o código acima não resulte em um erro.

Caso forem adicionadas novas linhas a fonte de dados da Tabela Dinâmica, podemos deixá-la atualizada carregando um novo cache a ela com o método .ChangePivotCache.

Sub atualizarTabelaDin()

    Set RangeAtualizado = Worksheets("Planilha1").Cells(1, 1).CurrentRegion

    Set CacheAtualizado = ActiveWorkbook.PivotCaches _
    .Create(SourceType:=xlDatabase, SourceData:=RangeAtualizado)

    Worksheets("TabelaDinamica").PivotTables("TabelaDinamica1").ChangePivotCache (CacheAtualizado)

End Sub

Perceba que, por se utilizar .CurrentRegion, se os dados estiverem incompletos a seleção poderá ocorrer de maneira incorreta.

Deletar Tabela Dinâmica

Podemos deletar a Tabela Dinâmica simplesmente apagando a aba onde ela está presente.

Sheets("TabelaDinamica").Delete

Ou podemos deletá-la individualmente através do método .Clear na propriedade .TableRange2.

Sub deletarTabelaDinamica()

    Worksheets("TabelaDinamica").PivotTables("TabelaDinamica1").TableRange2.Clear

End Sub

SuperExcelVBA.com é um site voltado ao aprendizado de VBA. Exemplos e explicações podem ter sido simplificados para maior e mais veloz compreensão. Estamos constantemente nos atualizando e corrigindo erros, porém não existe garantia sobre o conteúdo disponível no site. Todos os direitos reservados.

Excel ® é uma marca registrada da Microsoft Corporation.

INICIANDO

FUNDAMENTOS

RANGE

USERFORM

APROFUNDANDO