Excel Transformar Dados em tabela dinâmica através do VBA

[Gabriel Gallonetti]

Boa tarde!!

Rapaziada eu estou com um problema eu tenho uma planilha do serviço na qual eu após fazer toda uma limpa nela (que já está feita em VBA) eu preciso selecionar todas as células e transformar numa tabela dinâmica. e eu queria saber se teria como fazer deste jeito de moto automático. irei mandar a planilha base e a forma que eu estou tentando chegar. (não necessariamente precisaria ficar exatamente igual, mas algo parecido para criar um padrão de e-mail).

ExportedReport.rarBuscando informações...

Conforme mostrado aqui, ele precisa estar mais ou menos assim. separado nessas linhas e dessa forma. 

[Midori]

@Gabriel Gallonetti Crie a tabela dinâmica e arraste os campos: Categoria Financeira e Favorecido em Linhas e Vl. Previsto em Valores.

 

Uma vez criada a tabela dinâmica só terá que atualizar. Para facilitar isso você pode deixar os dados da planilha no formato de Tabela (Inserir > Tabela). Assim o range da tabela dinâmica sempre será o mesmo da outra.

[Gabriel Gallonetti]

@Midori Boa tarde!

Midori o que eu estou querendo é um código VBA que estará fazendo isso automaticamente para mim somente apertando um botão ou algo similar.

[Midori]

@Gabriel Gallonetti Se quiser automatizar isso pode tentar usar o gravador de macro e a partir do código gerado fazer as alterações necessárias.

[Gabriel Gallonetti]

@Midori como assim gravador de macro? isso dentro do próprio excel?

  Em 18/10/2021 às 20:11, Gabriel Gallonetti disse:

@Midori como assim gravador de macro? isso dentro do próprio excel?

Expandir  

Tentei gravar a macro e fazer os meus passos e não funcionou deu erro de depuração.

[Midori]

@Gabriel Gallonetti Em Exibir > Macros > Gravar Macro. Coloque para gravar e após a criação da tabela dinâmica pare o gravador. Feito isso o código estará no módulo onde você pode fazer alguma edição se for necessário.

[DJunqueira]

O Windows tem um sistema de compactação nativo .zip, esse formato pode ser acessado por todos e facilita a vida de que quer te ajudar.

 

 

[Gabriel Gallonetti]

@Midori Eu fui exatamente aí e este é o erro que está dando. 

E o código que me entrega é este. 

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A5:D178").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Plan1!R5C1:R178C4", Version:=6).CreatePivotTable TableDestination:= _
        "Planilha1!R3C1", TableName:="Tabela dinâmica1", DefaultVersion:=6
    Sheets("Planilha1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields( _
        "Categoria Financeira")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tabela dinâmica1").AddDataField ActiveSheet. _
        PivotTables("Tabela dinâmica1").PivotFields("Vl. Previsto"), _
        "Soma de Vl. Previsto", xlSum
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Favorecido")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("Tabela dinâmica1").RowAxisLayout xlTabularRow
End Sub
 

 

  Em 19/10/2021 às 11:28, Gabriel Gallonetti disse:

@Midori Eu fui exatamente aí e este é o erro que está dando. 

E o código que me entrega é este. 

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A5:D178").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Plan1!R5C1:R178C4", Version:=6).CreatePivotTable TableDestination:= _
        "Planilha1!R3C1", TableName:="Tabela dinâmica1", DefaultVersion:=6
    Sheets("Planilha1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields( _
        "Categoria Financeira")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tabela dinâmica1").AddDataField ActiveSheet. _
        PivotTables("Tabela dinâmica1").PivotFields("Vl. Previsto"), _
        "Soma de Vl. Previsto", xlSum
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Favorecido")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("Tabela dinâmica1").RowAxisLayout xlTabularRow
End Sub
 

Expandir  

o que eu realmente necessitava era que a planilha selecionasse o range de somente o que tem coisa escrita, porque só vai restar na planilha o que será usado na tabela.

  Em 19/10/2021 às 11:28, Gabriel Gallonetti disse:

 

Expandir  

 

[Midori]

@Gabriel Gallonetti Veja se assim resolve. Antes de rodar crie uma aba com o nome Dinâmica.

 

Sub TabelaDinamica()
    Dim Tabela          As PivotTable
    Dim Favorecido      As PivotField
    Dim Categoria       As PivotField
    Dim PlanPagamento   As Worksheet
    Dim PlanDinamica    As Worksheet
    
    Set PlanDinamica = ThisWorkbook.Sheets("Dinâmica")
    Set PlanPagamento = ThisWorkbook.Sheets("RptPagamentoCategoriaFinac")
    Set Tabela = ThisWorkbook.PivotCaches.Create(xlDatabase, _
        PlanPagamento.[B5].CurrentRegion).CreatePivotTable( _
        PlanDinamica.[A1], "TabelaDinamica")
        
    Call Tabela.AddDataField(Tabela.PivotFields("Vl. Previsto"), "Soma de Vl. Previsto", xlSum)
    
     With Tabela
        .InGridDropZones = True
        .DisplayFieldCaptions = True
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .AllowMultipleFilters = True
        .SortUsingCustomLists = True
        .ShowValuesRow = True
        .RowAxisLayout xlTabularRow
    End With
    
    Set Categoria = Tabela.PivotFields("Categoria Financeira")
    Set Favorecido = Tabela.PivotFields("Favorecido")
    
    Categoria.Orientation = xlRowField
    Categoria.Position = 1
    Favorecido.Orientation = xlRowField
    Favorecido.Position = 2
    
    Categoria.PivotFilters.Add2 xlCaptionEquals, Value1:="Salario"
    Favorecido.PivotFilters.Add2 xlCaptionContains, Value1:="FOLHA DE PAGAMENTO"
End Sub

 

[Gabriel Gallonetti]

@Midori  ele está dando este erro, e criei a Dinâmica.

 

  Em 19/10/2021 às 14:17, Gabriel Gallonetti disse:

@Midori  ele está dando este erro, e criei a Dinâmica.

 

Expandir  

Midori parece que houve um mal entendido da minha parte em explicar lá em cima aparece algumas informações pórem as unicas informaçõe que vão se repetir todos os dias são essas 

 

são as colunas e os valores que são sempre os mesmos.

[Midori]

  Em 19/10/2021 às 14:17, Gabriel Gallonetti disse:

Midori parece que houve um mal entendido da minha parte em explicar lá em cima aparece algumas informações pórem as unicas informaçõe que vão se repetir todos os dias são essas

Expandir  

Todas as informações estarão na tabela dinâmica, mas as únicas que vão aparecer são as do critério do filtro.

 

Não entendi o que quer dizer com "repetir todos os dias".

 

Conseguiu rodar a macro que postei? Rodei aqui e ficou no formato do seu exemplo no primeiro post.

[Gabriel Gallonetti]

@Midori não consegui rodar ele da aquele erro Subscrito fora do intervalo

é porque vi que você adicinou essas palavras no filtro "salario" "folha de pagamento" e essas palavras vão mudar entendeu? não são as mesmas diariamente.

 

  Em 19/10/2021 às 14:46, Gabriel Gallonetti disse:

@Midori não consegui rodar ele da aquele erro Subscrito fora do intervalo

é porque vi que você adicinou essas palavras no filtro "salario" "folha de pagamento" e essas palavras vão mudar entendeu? não são as mesmas diariamente.

 

Expandir  

eu queria algo que somente formasse a tabela dinamica baseado ali em cima igual coloquei nos filtros mesmo que serão (categoria financeira, valor previsto e favorecido

[Midori]

Rodei na planilha que você anexou e a tabela é criada normalmente.

 

Se tentar rodar antes de criar a aba Dinâmica vai dar o erro de subscrito fora do intervalo.

  Em 19/10/2021 às 14:46, Gabriel Gallonetti disse:

é porque vi que você adicinou essas palavras no filtro "salario" "folha de pagamento" e essas palavras vão mudar entendeu? não são as mesmas diariamente.

 

Expandir  

Então acho melhor fazer como cometei no primeiro post que é criar a tabela manualmente.

 

E a macro só será usada para atualizar a dinâmica e mudar essas palavras no filtro.

[Gabriel Gallonetti]

@Midori

Tentei novamente e não foi, nem mesmo gravar o macro. acho que como muda diariamente muda até mesmo a quantiade de linhas ele não tem como fazer isso.

[Midori]

@Gabriel Gallonetti Vamos voltar para o início, esqueça a macro por enquanto. Abra a planilha do anexo sem edição e só com a aba do Pagamento. Selecione a célula B5 e crie a tabela dinamica manualmente em Inserir > Tabela Dinâmica > Ok. Deixe a tabela no formato da primeira imagem: Coloque Vl. Previsto em Valores, Categoria Financeira e Favorecido em Linhas.

 

Se der tudo certo explique qual é o critério para mudar as palavras. É para isso que acho que deve ser usada uma macro para automatizar o critério do filtro da tabela dinâmica. No seu exemplo o critério das palvaras é "salario" e "folha de pagamento". E se entendi direito essas palavras podem mudar.

[Gabriel Gallonetti]

@Midori Bom dia!

 

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A5").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "RptPagamentoCategoriaFinac!R5C1:R26C5", Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="Planilha1!R3C1", TableName:= _
        "Tabela dinâmica1", DefaultVersion:=xlPivotTableVersion10
    Sheets("Planilha1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields( _
        "Categoria Financeira")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tabela dinâmica1").AddDataField ActiveSheet. _
        PivotTables("Tabela dinâmica1").PivotFields("Vl. Previsto"), _
        "Soma de Vl. Previsto", xlSum
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Favorecido")
        .Orientation = xlRowField
        .Position = 2
    End With
End Sub
 

O código ficou assim e eu fiz da forma mais simples possível, eu cliquei em A5, (é a coluna certa mesmo) e gerei a tabela dinamica e arrastei cada um para o seu lugar, parei de gravar a macro e encerrei, logo após isso exclui a tabela, e tentei rodar a macro e ele deu o seguinte erro

[Midori]

@Gabriel Gallonetti Não vejo motivo para continuar tentando usar outra macro sem fazer as edições necessárias após a gravação. O código que postei funcionou na sua planilha, já que quer uma macro para criar a dinâmica acho que devia tentar usar aquele em vez de gravar outra. Leu o meu último comentário?

[Gabriel Gallonetti]

@Midori irei te passar a planilha na qual estou tentendo utilizar o seu código, para que você veja.

 

ExportedReport.rarBuscando informações...

[Midori]

@Gabriel Gallonetti Executei o meu código e a tabela dinâmica foi criada normalmente e sem erro, só tem que criar a aba Dinâmica antes como eu já tinha comentado. E só não filtrou nada porque a sua planilha não tem mais as informações de saldo e folha.

[Gabriel Gallonetti]

@Midori Boa tarde, então testei o código e realmente funcionou, pórem tem esse caso, porque esse saldo e folhas são categorias financeiras que mudam todos dias (sempre são as mesmas, pórem em alguns dias aparecem umas e outros as outras) teria como eu colocar no script todas elas? aí assim que rodar o macro ele formar a tabela toda correta? se caso sim, você poderia por gentileza me ensinar onde eu deveria alterar no script para adicionar o nome de todas elas?

[Midori]

@Gabriel Gallonetti Essa macro que cria a tabela dinâmica só precisa rodar uma vez. Depois é só atualizar e aplicar o filtro. E se os dados estiverem formatados como Tabela (Inserir > Tabela) o range será ajustado automaticamente. No seu primeiro post os critérios do filtro eram "saldo" e "folha de pagamento", para mudar isso é só passar esses valores para o filtro. Uma forma seria com uma Sub com os dois parâmetros, assim,

 

Sub AtualizaTabela(FiltroCategoria As String, FiltroFavorecido As String)
    Dim Tabela      As PivotTable
    Dim Favorecido  As PivotField
    Dim Categoria   As PivotField
    
    Set Tabela = ThisWorkbook.Sheets("Dinâmica").PivotTables("TabelaDinamica")
    Set Categoria = Tabela.PivotFields("Categoria Financeira")
    Set Favorecido = Tabela.PivotFields("Favorecido")
    
    Categoria.ClearAllFilters
    Favorecido.ClearAllFilters
    Call Categoria.PivotFilters.Add2(Type:=xlCaptionEquals, Value1:=FiltroCategoria)
    Call Favorecido.PivotFilters.Add2(Type:=xlCaptionContains, Value1:=FiltroFavorecido)
    Tabela.PivotCache.Refresh
End Sub

 

Aí é só passar os argumentos para a Sub, p.ex,

Sub Macro()
    Call AtualizaTabela("Consorcios contemplados (Despésa)", "GAPLAN")
End Sub

 

Obs: No filtro do favorecido usei CaptionContains porque os dados estão com espaço.