Tabela Dinâmica VBA Excel
As tabelas dinâmicas são o coração do resumo do relatório de uma grande quantidade de dados. Também podemos automatizar o processo de criação de uma tabela dinâmica por meio da codificação VBA. Eles são parte importante de qualquer relatório ou painel, no Excel é fácil criar tabelas com um botão, mas no VBA temos que escrever alguns códigos para automatizar nossa tabela dinâmica, antes do Excel 2007 e suas versões mais antigas no VBA não precisávamos crie um cache para tabelas dinâmicas, mas no Excel 2010 e suas versões mais recentes, caches são necessários.
O VBA pode economizar muito tempo para nós em nosso local de trabalho, embora dominá-lo não seja tão fácil, mas vale a pena gastar tempo para aprender isso. Levei 6 meses para entender o processo de criação de tabelas dinâmicas por meio do VBA. Você sabe o que esses 6 meses fizeram maravilhas para mim, porque cometi muitos erros ao tentar criar a tabela dinâmica.
Mas a verdade é que aprendi com meus erros e agora estou escrevendo este artigo para mostrar a você como criar tabelas dinâmicas usando código.
Com apenas um clique de um botão, podemos criar relatórios.
Etapas para criar uma tabela dinâmica em VBA
Você pode baixar este modelo de tabela dinâmica VBA aqui - modelo de tabela dinâmica VBAPara criar uma tabela dinâmica, é importante ter dados. Para isso criei alguns dados fictícios, você pode baixar a pasta de trabalho para seguir comigo com os mesmos dados.
Etapa 1: A tabela dinâmica é um objeto para fazer referência à tabela dinâmica e declarar a variável como tabelas dinâmicas.
Código:
Sub Tabela Dinâmica () Dim PTable como Tabela Dinâmica End Sub
Etapa 2: antes de criar uma tabela dinâmica, primeiro precisamos criar um cache dinâmico para definir a fonte dos dados.
Na tabela dinâmica de planilha regular, sem nos incomodar, criaremos um cache dinâmico em segundo plano. Mas no VBA temos que criar.
Para isso defina a variável um PivotCache.
Código:
Dim PCache como PivotCache
Etapa 3: para determinar o intervalo de dados do pivô, defina a variável como um intervalo.
Código:
Dim PRange As Range
Passo 4: Para inserir uma tabela dinâmica, precisamos de uma folha separada para adicionar planilha para tabela dinâmica declarar a variável como uma planilha.
Código:
Dim PSheet como planilha
Passo 5: Da mesma forma para referenciar os dados que contém a planilha, declare mais uma variável como planilha.
Código:
Dim DSheet As Worksheet
Passo 6: Finalmente, para encontrar a última linha e coluna usadas, defina mais duas variáveis como Longas.
Código:
Dim LR As Long Dim LC As Long
Etapa 7: agora precisamos inserir uma nova planilha para criar uma tabela dinâmica. Antes disso, se houver alguma planilha dinâmica, precisamos excluí-la.
Etapa 8: Agora defina a variável de objeto PSheet e DSheet para Pivot Sheet e Data Sheet, respectivamente.
Etapa 9: Encontre a última linha e a última coluna usadas na folha de dados.
Etapa 10: Agora defina o intervalo do pivô usando a última linha e a última coluna.
Isso definirá o intervalo de dados perfeitamente. Ele selecionará automaticamente o intervalo de dados, mesmo se houver qualquer adição ou exclusão de dados na folha de dados.
Etapa 11: Antes de criar uma tabela dinâmica, precisamos criar um cache dinâmico. Defina a variável de cache dinâmico usando o código VBA abaixo.
Etapa 12: Agora crie uma tabela dinâmica em branco.
Etapa 13: Após inserir a tabela dinâmica, precisamos inserir o campo de linha primeiro. Portanto, vou inserir o campo de linha como minha coluna País.
Observação: baixe a pasta de trabalho para entender as colunas de dados.
Etapa 14: Agora, mais um item irei inserir no campo de linha como o segundo item de posição. Vou inserir o produto como o segundo item de linha no campo de linha.
Passo 15: Depois de inserir as colunas no campo da linha, precisamos inserir os valores no campo da coluna. Vou inserir o “Segmento” no campo da coluna.
Etapa 16: Agora precisamos inserir números no campo de dados. Portanto, insira “Vendas” no campo de dados.
Etapa 17: Concluímos a parte do resumo da tabela dinâmica, agora precisamos formatar a tabela. Para formatar a tabela dinâmica, use o código abaixo.
Para mostrar os itens de valores arquivados por linha em forma tabular, adicione o código abaixo na parte inferior.
Ok, terminaremos se executarmos este código usando a tecla F5 ou manualmente, então devemos obter a tabela dinâmica como esta.
Assim, usando a codificação VBA, podemos automatizar o processo de criação de uma tabela dinâmica.
Para sua referência, forneci o código abaixo.
Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
