VBA – Guia para Tabelas Dinâmicas

Este tutorial demonstrará como trabalhar com tabelas dinâmicas usando o VBA.

As tabelas dinâmicas são ferramentas de resumo de dados que podem ser usadas para extrair insights e resumos importantes de seus dados. Vejamos um exemplo: temos um conjunto de dados de origem nas células A1:D21 que contém os detalhes dos produtos vendidos, mostrados abaixo:

 

Uso de GetPivotData para Obter um Valor

Suponha que você tenha uma tabela dinâmica chamada Tabela dinâmica1 com Vendas no campo Valores, Produto  como o campo Linhas e Região como o campo Colunas. Você pode usar o método PivotTable.GetPivotData para retornar valores de tabelas dinâmicas.

O código a seguir retornará US$ 1.130,00 (o total de vendas para a região leste) da tabela dinâmica:

MsgBox ActiveCell.PivotTable.GetPivotData("Vendas", "Região", "Leste")

Nesse caso, Vendas é o “DataField”, “Field1” é a Região e “Item1” é Leste.

O código a seguir retornará US$ 980,00 (o total de vendas do Produto ABC na Região Norte) da tabela dinâmica:

MsgBox ActiveCell.PivotTable.GetPivotData("Vendas", "Produto", "ABC", "Região", "Norte")

Nesse caso, Vendas é o “DataField”, “Field1” é Produto, “Item1” é ABC, “Field2” é Região e “Item2” é Norte.

Você também pode incluir mais de dois campos.

A sintaxe de GetPivotData é:

GetPivotData(DataField, Field1, Item1, Field2, Item2…) onde:

Parâmetro	Descrição
Datafield	Campo de dados, como vendas, quantidade etc., que contém números.
Field 1	Nome de um campo de coluna ou linha na tabela.
Item 1	Nome de um item no Field 1 (opcional).
Field 2	Nome de um campo de coluna ou linha na tabela (opcional).
Item 2	Nome de um item no Field 2 (Opcional).

Criação de uma tabela dinâmica em uma planilha

Para criar uma tabela dinâmica com base no intervalo de dados acima, na célula J2 da Planilha 1 da pasta de trabalho ativa, usaríamos o código a seguir:

Worksheets("Planilha1").Cells(1, 1).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
 "Planilha1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _
  TableDestination:="Planilha1!R2C10", TableName:="Tabela Dinâmica1", DefaultVersion _
  :=xlPivotTableVersion15

Sheets("Planilha1").Select

O resultado é:

Criando uma Tabela Dinâmica em uma Nova Planilha

Para criar uma tabela dinâmica com base no intervalo de dados acima, em uma nova planilha, da pasta de trabalho ativa, usaríamos o seguinte código:

Worksheets("Planilha1").Cells(1, 1).Select

Sheets.Add

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
 "Planilha1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _
  TableDestination:="Planilha2!R3C1", TableName:="Tabela Dinâmica1", DefaultVersion _
  :=xlPivotTableVersion15

  Sheets("Planilha2").Select

Adição de Campos à Tabela Dinâmica

Você pode adicionar campos à tabela dinâmica recém-criada chamada Tabela Dinâmica1 com base no intervalo de dados acima. Observação: a planilha que contém a tabela dinâmica precisa ser a planilha ativa.

Para adicionar Produto ao campo Linhas, você usaria o seguinte código:

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Produto").Orientation = xlRowField

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Produto").Position = 1

Para adicionar Região ao Campo Colunas, você usaria o seguinte código:

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Região").Orientation = xlColumnField

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Região").Position = 1

Para adicionar Vendas à seção Valores com o formato de número de moeda, use o seguinte código:

ActiveSheet.PivotTables("Tabela Dinâmica1").AddDataField ActiveSheet.PivotTables( _
        "Tabela Dinâmica1").PivotFields("Vendas"), "Soma de Vendas", xlSum

    With ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Soma de Vendas")

        .NumberFormat = "$#,##0.00"

End With

O resultado é:

Como Alterar o Layout do Relatório da Tabela Dinâmica

Você pode alterar o layout do relatório da tabela dinâmica. O código a seguir alterará o layout do relatório de sua tabela dinâmica para o formulário tabular:

ActiveSheet.PivotTables("Tabela Dinâmica1").TableStyle2 = "PivotStyleLight18"

Exclusão de uma Tabela Dinâmica

É possível excluir uma tabela dinâmica usando o VBA. O código a seguir excluirá a tabela dinâmica chamada Tabela Dinâmica1 na ActiveSheet:

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotSelect "", xlDataAndLabel, True
Selection.ClearContents

Formatar Todas as Tabelas Dinâmicas em uma Pasta de Trabalho

É possível formatar todas as tabelas dinâmicas em uma pasta de trabalho usando o VBA. O código a seguir usa uma estrutura de loop para percorrer todas as planilhas de uma pasta de trabalho e formata todas as tabelas dinâmicas na pasta de trabalho:

Sub FormatarTodasTabelasDinamicasNaPasta()
Dim wks As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim pt As PivotTable

For Each wks In wb.Sheets
   For Each pt In wks.PivotTables
      pt.TableStyle2 = "PivotStyleLight15"
   Next pt
Next wks

End Sub

Para saber mais sobre como usar loops no VBA, clique aqui.

Remoção de Campos de uma Tabela Dinâmica

É possível remover campos em uma tabela dinâmica usando o VBA. O código a seguir removerá o campo Produto na seção Linhas de uma tabela dinâmica chamada Tabela Dinâmica1 na ActiveSheet:

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Produto").Orientation = _
xlHidden

Criação de um Filtro

Uma tabela dinâmica chamada Tabela Dinâmica1 foi criada com Produto na seção Linhas e Vendas na seção Valores. Você também pode criar um filtro para sua tabela dinâmica usando o VBA. O código a seguir criará um filtro com base em Região na seção Filtros:

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Região").Orientation = xlPageField

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Região").Position = 1

Para filtrar a tabela dinâmica com base em um único item de relatório, nesse caso a região leste, você usaria o seguinte código:

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Região").ClearAllFilters

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Região").CurrentPage = _
"Leste"

Digamos que você queira filtrar sua tabela dinâmica com base em várias regiões, neste caso Leste e Norte, você usaria o seguinte código:

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Região").Orientation = xlPageField

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Região").Position = 1

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Região"). _
EnableMultiplePageItems = True

With ActiveSheet.PivotTables("Tabela Dinâmica1").PivotFields("Região")

   .PivotItems("Sul").Visible = False

   .PivotItems("Oeste").Visible = False

End With

Atualização da Tabela Dinâmica

Você pode atualizar sua tabela dinâmica no VBA. Você usaria o código a seguir para atualizar uma tabela específica chamada Tabela Dinâmica1 no VBA:

ActiveSheet.PivotTables("Tabela Dinâmica1").PivotCache.Refresh