Guia VBA para tabelas dinâmicas

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

As tabelas dinâmicas são ferramentas de resumo de dados que você pode usar para extrair ideias e resumos importantes de seus dados. Vejamos um exemplo: temos um conjunto de dados de origem nas células A1: D21 contendo os detalhes dos produtos vendidos, mostrados abaixo:

Usando GetPivotData para obter um valor

Suponha que você tenha uma tabela dinâmica chamada Tabela Dinâmica1 com Vendas no campo Valores / Dados, 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á $ 1.130,00 (o total de vendas para a Região Leste) da Tabela Dinâmica:

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

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

O código a seguir retornará $ 980 (as vendas totais do Produto ABC na Região Norte) da Tabela Dinâmica:

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

Neste caso, Vendas é o “Campo de Dados”, “Campo1” é Produto, “Item1” é ABC, “Campo2” é Região e “Item2” é Norte.

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

A sintaxe para GetPivotData é:

GetPivotData (Campo de dados, Field1, Item 1, Field2, Item 2… ) Onde:

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

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

Para criar uma Tabela Dinâmica com base no intervalo de dados acima, na célula J2 na Planilha1 da pasta de trabalho Ativa, usaríamos o seguinte código:

1234567891011

Planilhas ("Folha1"). Células (1, 1) .SelecionarActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Planilha1! R1C1: R21C4", Versão: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Folhas ("Folha1"). Selecione

O resultado é:

Criação de uma tabela dinâmica em uma nova folha

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:

12345678910111213

Planilhas ("Folha1"). Células (1, 1) .SelecionarSheets.AddActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Planilha1! R1C1: R21C4", Versão: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Folhas ("Folha2"). Selecione

Adicionando 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. Nota: A planilha que contém sua Tabela Dinâmica precisa ser a Planilha Ativa.

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

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Produto"). Orientação = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Produto"). Posição = 1

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

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Região"). Orientação = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Região"). Posição = 1

Para adicionar vendas à seção de valores com o formato do número da moeda, você usaria o seguinte código:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"Tabela Dinâmica1"). Campos Dinâmicos ("Vendas"), "Soma das Vendas", xlSumCom ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Soma das vendas").NumberFormat = "$ #, ## 0.00"Terminar com

O resultado é:

Alterando o Layout do Relatório da Tabela Dinâmica

Você pode alterar o Layout do Relatório de sua Tabela Dinâmica. O código a seguir mudará o layout do relatório de sua tabela dinâmica para o formato tabular:

1	ActiveSheet.PivotTables ("PivotTable1"). TableStyle2 = "PivotStyleLight18"

Excluindo uma Tabela Dinâmica

Você pode excluir uma Tabela Dinâmica usando o VBA. O código a seguir excluirá a Tabela Dinâmica chamada Tabela Dinâmica1 na Planilha Ativa:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueSelection.ClearContents

Formate todas as tabelas dinâmicas em uma pasta de trabalho

Você pode 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 excluir todas as tabelas dinâmicas da pasta de trabalho:

12345678910111213

Sub FormattingAllThePivotTablesInAWorkbook ()Dim wks como planilhaDim wb como pasta de trabalhoDefinir wb = ActiveWorkbookDim pt como tabela dinâmicaPara cada semana em wb. FolhasPara cada ponto em wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Próximo ptPróximas semanasEnd Sub

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

Removendo Campos de uma Tabela Dinâmica

Você pode 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 Planilha Ativa:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Produto"). Orientação = _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 na região na seção Filtros:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Região"). Posição = 1

Para filtrar sua Tabela Dinâmica com base em um Único Item de Relatório, neste caso a região Leste, você usaria o seguinte código:

12345

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). 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:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Região"). Posição = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Região"). _EnableMultiplePageItems = TrueCom ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Região").PivotItems ("Sul"). Visível = Falso.PivotItems ("Oeste"). Visível = FalsoTerminar com

Atualizando sua tabela dinâmica

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

1	ActiveSheet.PivotTables ("PivotTable1"). PivotCache.Refresh