Como criar uma tabela dinâmica com vba?

Antes de entregar este guia e começar a usar o VBA para criar uma tabela dinâmica , deixe-me dizer uma coisa.

Aprendi a usar VBA há apenas SEIS anos. E a primeira vez que escrevi código de macro para criar uma tabela dinâmica, foi um fracasso.

Desde então, aprendi mais com minha codificação incorreta do que com códigos que realmente funcionam.

Hoje vou mostrar uma maneira simples de automatizar suas tabelas dinâmicas usando código de macro.

Normalmente, quando você insere uma tabela dinâmica em uma planilha, isso acontece através de um processo simples, mas todo esse processo é tão rápido que você nem percebe o que aconteceu.

No VBA todo esse processo é o mesmo, apenas é executado usando código. Neste guia, mostrarei cada etapa e explicarei como escrever código para ela.

Basta olhar o exemplo abaixo, onde você pode executar esse código de macro com um botão e ele retorna uma nova tabela dinâmica em uma nova planilha em um piscar de olhos.

Sem mais delongas, vamos começar a escrever nosso código de macro para criar uma tabela dinâmica.

As 8 etapas simples para escrever código de macro em VBA para criar uma tabela dinâmica no Excel

Para sua comodidade, dividi todo o processo em 8 etapas simples. Após seguir essas etapas, você poderá automatizar todas as suas tabelas dinâmicas.

Certifique-se de baixar este arquivo aqui para acompanhar.

1. Declarar variáveis

O primeiro passo é declarar as variáveis que precisamos usar em nosso código para definir coisas diferentes.

 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long

No código acima declaramos:

1. PSheet: para criar uma planilha para uma nova tabela dinâmica.
2. DSheet: para ser utilizado como ficha técnica.
3. PChache: Use como nome para o cache da tabela dinâmica.
4. PTable: Use como nome para nossa tabela dinâmica.
5. PRange: para definir o intervalo de dados de origem.
6. LastRow e LastCol: para obter a última linha e coluna do nosso intervalo de dados.

2. Insira uma nova planilha

Antes de criar uma tabela dinâmica, o Excel insere uma planilha em branco e cria uma nova tabela dinâmica nela.

E o código abaixo fará a mesma coisa por você.

Ele irá inserir uma nova planilha com o nome “Tabela Dinâmica” antes da planilha ativa e se já existir uma planilha com o mesmo nome, irá excluí-la primeiro.

Após inserir uma nova planilha, este código definirá o valor da variável PSheet na planilha da tabela dinâmica e DSheet na planilha de dados de origem.

 'Declare Variables On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")

Certifique-se de substituir os nomes das planilhas no código pelos nomes que você possui em seus dados.

3. Defina o intervalo de dados

Agora, a próxima etapa é definir o intervalo de dados da planilha de origem. Aqui você precisa cuidar de uma coisa: você não pode especificar um intervalo de origem fixo.

Você precisa de um código que possa identificar todos os dados na planilha de origem. E abaixo está o código:

 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Este código começará na primeira célula da tabela de dados e selecionará até a última linha e depois até a última coluna.

E, finalmente, defina este intervalo selecionado como fonte. A melhor parte é que você não precisa alterar a fonte de dados todas as vezes ao criar a tabela dinâmica.

4. Crie um cache dinâmico

No Excel 2000 e superior, antes de criar uma tabela dinâmica, você deve criar um cache dinâmico para definir a fonte de dados.

Normalmente, quando você cria uma tabela dinâmica, o Excel cria automaticamente um cache dinâmico sem perguntar, mas quando você precisa usar o VBA, é necessário escrever algum código para isso.

 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")

Este código funciona de duas maneiras: primeiro, definindo um cache dinâmico usando uma fonte de dados e, segundo, definindo o endereço da célula na planilha recém-inserida para inserir a tabela dinâmica.

Você pode alterar a posição da tabela dinâmica modificando este código.

5. Insira uma tabela dinâmica em branco

Após o cache dinâmico, a próxima etapa é inserir uma tabela dinâmica vazia. Lembre-se de que ao criar uma tabela dinâmica, você sempre obtém primeiro um pivô vazio e depois define todos os valores, colunas e linhas.

Este código fará a mesma coisa:

 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

Este código cria uma tabela dinâmica vazia e a chama de “SalesPivotTable”. Você pode alterar esse nome no próprio código.

6. Insira campos de linha e coluna

Depois de criar uma tabela dinâmica vazia, a próxima etapa é inserir campos de linha e coluna, como você faz normalmente.

Para cada campo de linha e coluna, você deve escrever um código. Aqui queremos adicionar anos e meses no campo linha e áreas no campo coluna.

Aqui está o código:

 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With

Neste código você mencionou ano e mês como dois campos. Agora, se você olhar o código, verá que também existe um número de posição. Este número de posição define a sequência de campos.

Cada vez que precisar adicionar vários campos (Linha ou Coluna), especifique sua posição. E você pode modificar os campos alterando seu nome no código.

7. Insira um campo de dados

O principal é definir o campo de valor em sua tabela dinâmica.

O código para definição de valores difere da definição de linhas e colunas porque aqui precisamos definir a formatação de números, posições e funções.

 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With

Você pode adicionar o valor como um campo de valor com o código acima. E esse código irá formatar os valores como um número com separador (,).

Usamos xlsum para somar os valores, mas você também pode usar xlcount e outras funções.

8. Formate a tabela dinâmica

Em última análise, você precisa usar código para formatar sua tabela dinâmica. Geralmente há uma formatação padrão em uma tabela dinâmica, mas você pode alterar essa formatação.

Com o VBA, você pode definir o estilo de formatação no código.

O código é:

 'Format Pivot TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

O código acima aplicará faixas de linha e o estilo “Pivot Style Medium 9”, mas você também pode usar outro estilo deste link .

Finalmente, seu código está pronto para uso.

[CÓDIGO COMPLETO] Use VBA para criar uma tabela dinâmica no Excel – Copiar e colar macro

 Sub InsertPivotTable() 'Macro By Exceladvisor.org 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable") 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" End Sub

Tabela dinâmica em planilha existente

O código que usamos acima cria uma tabela dinâmica em uma nova planilha, mas às vezes você precisa inserir uma tabela dinâmica em uma planilha que já está na pasta de trabalho.

No código acima (tabela dinâmica em nova planilha), na parte onde você escreveu o código para inserir uma nova planilha, dê um nome a ela. Por favor, faça algumas alterações no código.

Não se preocupe; Eu vou te mostrar.

Primeiro você precisa especificar a planilha (já na pasta de trabalho) onde deseja inserir sua tabela dinâmica.

E para isso você deve utilizar o código abaixo:

Em vez de inserir uma nova planilha, você deve especificar o nome da planilha na variável PSheet.

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”)

Há um pouco mais a fazer. O primeiro código que você usou exclui a planilha de mesmo nome (se existir) antes de inserir o pivô.

Ao inserir uma tabela dinâmica na planilha existente, você já pode ter uma tabela dinâmica com o mesmo nome.

O que estou dizendo é que você precisa primeiro remover esse pivô.

Para fazer isso, é necessário adicionar o código que deve remover o pivô de mesmo nome da planilha (se houver) antes de inserir um novo.

Aqui está o código que você precisa adicionar:

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”) Worksheets("PivotTable").Activate On Error Resume Next ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear

Deixe-me dizer o que esse código faz.

Primeiro, ele apenas define PSheet como a planilha na qual deseja inserir a tabela dinâmica já em sua pasta de trabalho e define Planilhas de Dados como DSheet.

Depois disso, ele ativa a planilha e remove dela a “Tabela Dinâmica de Vendas”.

Importante: Se os nomes das planilhas na sua pasta de trabalho forem diferentes, você poderá alterá-los no código. Destaquei o código onde você precisa fazer isso.

No final,

Usando este código podemos automatizar suas tabelas dinâmicas. E a melhor parte é que é uma configuração única; depois disso, precisamos apenas de um clique para criar a tabela dinâmica e você pode economizar muito tempo. Agora me diga uma coisa.

Você já usou código VBA para criar uma tabela dinâmica?

Por favor, compartilhe suas opiniões comigo na caixa de comentários; Gostaria de compartilhá-los com você e compartilhar essa dica com seus amigos.

Artigos semelhantes:

• Adicione ou remova o total geral em uma tabela dinâmica no Excel
• Adicione o total acumulado a uma tabela dinâmica no Excel
• Atualizar automaticamente uma tabela dinâmica no Excel
• Adicione um campo calculado e um item
• Excluir uma tabela dinâmica no Excel