Essa postagem nasceu com o intuito de orientá-los a construir análises evolutivas relacionadas ao negócio. Como sou uma pessoa da área financeira, os dados que utilizaremos são sobre finanças, mais especificamente sobre custos com prestadores de serviços.

Sempre que atendo um departamento vejo o mesmo problema estrutural: tabelas que crescem lateralizadas, falta de padrão, observações no final da tabela, subtotais no meio das tabelas, utilização de nomes (equipes, pessoas, empresas) sem códigos de cadastro enfim… Tudo isso contribui para uma coisa: o retrabalho maçante para conseguir uma simples análise de “valor/mês + variação mensal”. Portanto a intenção aqui é “reprogramar” o que você conhece sobre controle, conceder relatórios mais robustos utilizando a lógica de macro para micro e a partir do que você quer demonstrar conseguiremos automatizar a formatação da base de dados. Isso porque estamos falando de um controle manual e utilizar a VBA pode e costuma ser uma boa saída.

1º Passo – Entendendo o Arquivo Excel Original

Geralmente a área de negócio coleta dados de diversos lugares/fontes para alimentar um controle único. Esses dados costumam vir de um relatório detalhado e no controle o que a área quer colocar é o valor total por equipe/prestador/empresa para apresentar de maneira global por mês/ano. O objetivo do primeiro passo é analisar como está esse controle e se ele precisa de uma reorganização. O que geralmente encontro é uma planilha assim:

Note que a planilha de controle original possui todos os dados que precisamos, mas estão organizados de uma forma não tão eficiente que atrapalha qualquer estudo. Vamos corrigir os primeiros pontos: lateralização da tabela, retirar os subtotais, transportar os meses para as colunas e subir a codificação de cadastro das equipes, pois pode ser que um dia essa equipe mude de nome.

Após as modificações teremos uma base de dados limpa assim:

Agora o objetivo é fazer o reporte global mensal -> valor total por empresa -> e possibilitar a visão de total por equipe/mês.

2º Passo – Reporte Macro para Micro

Num primeiro momento, apenas para brincar com as possibilidades, você pode fazer uma aba de “Scripts” onde ficará toda a central de tabelas dinâmicas. Exemplo:

A partir daí você já pode fazer o “Resumo” que é o relatório que de fato o gestor vai analisar os totais, as variações, qual equipe merece atenção ou revisão de cálculo. Nota: veja que as dinâmicas estão lateralizadas, isso porque os dados crescerão para baixo, então se empilharmos as tabelas isso dará erro e teremos retrabalho.

Veja que o resumo abaixo está respeitando a lógica do “Macro para Micro” e é interativo, possibilitando o gestor de ver os valores globais, por equipe e realizar o filtro por empresa. O filtro por empresa impacta os dois gráficos:

3º Passo: automatizar formatação da base de dados

Neste exemplo pegamos um controle básico, construimos uma base de dados limpa e fizemos o relatório a partir dele. “Apagamos o fogo”, mas comentei que a área de negócio busca os dados em fontes diversas, muitas vezes do próprio sistema. Então é interessante montar uma solução a médio/longo prazo para que a rotina seja suavizada e a área demore menos tempo para atualizar o controle.

Caso o sistema gere arquivos em Excel ou CSV, podemos atuar com a VBA para formatação automática para alimentar a base de dados. Neste sentido construí um código de VBA simples para que vocês entendam a lógica de como a automatização funciona.

Já definimos a estrutura da base de dados necessária para reportar essas informações, mas entendemos também que a área de negócio consome esses dados de outras fontes como o sistema (ERP) institucional, seja ele comercial (TOTVS, SAP, MV etc.) seja ele interno/próprio. Então o objetivo aqui é aplicar uma solução a partir do momento que você exporta o relatório e salva na rede. A solução é com VBA e vamos entender, de maneira sucinta, como ela funciona para que você adapte para a sua realidade.

VBA ou Visual Basic for Applications é uma linguagem de programação incorporada nos aplicativos da Microsoft, como o Excel. Ela permite aos usuários criar macros e scripts que automatizam tarefas repetitivas, personalizam a interface do usuário e expandem a funcionalidade padrão dos aplicativos. Neste caso a utilizaremos para personalizar e automatizar uma formatação. No Excel ela fica disponível nos atalhos Alt + F11 (Editor VBA) ou seguindo o caminho descrito abaixo:

Acessar a guia Desenvolvedor e clicar em Visual Basic. Se a guia Desenvolvedor não estiver visível, você precisará ativá-la em “Arquivo” > “Opções” > “Personalizar Faixa de Opções”.

Raciocínio do código

Vamos trabalhar pensando em um “De -> Para”. O código precisa entender onde estão os dados e como você quer deixá-los dispostos, então todo detalhe é crucial (nome do arquivo, endereço da rede, fonte das letras, cores, fórmulas etc.). No exemplo que vou demonstrar o relatório surge de um sistema próprio, simples, que gera 1 relatório por empresa, então o profissional precisa logar na empresa que ele deseja capturar os dados e gerar o relatório do mês:

O Relatório pode ser exportado em PDF, CSV, TXT e Excel (xlsx). Optaremos pelo Excel e ao terminar o processo o relatório concedido pelo sistema é esse:

Veja que esse relatório possui mais dados. Os valores então abertos por serviço, a coluna da empresa vem primeiro, portanto a ordem das demais colunas não batem com a ordem da base. O código VBA deve tranformar esse relatório detalhado na base de dados e colar essas informações transformadas no final da base (para quando o profissional for gerar os dados do mês de abril/25 por exemplo).

Hora de Codar – Estrutura do Código VBA

1. Declarações e Configurações Iniciais:
   Dim wbOriginal As Workbook
Dim wsModelo As Worksheet
Dim wsFormatado As Worksheet
Dim wsDinamica As Worksheet
Dim ultimaLinha As Long
Dim TabelaDinamica As PivotTable
Dim intervaloDados As Range
   
   Aqui, estamos declarando várias variáveis que vamos usar mais tarde:
   wbOriginal: Guarda o arquivo de Excel que estamos usando.
   wsModelo: Representa a planilha de onde vamos copiar os dados.
   wsFormatado: Representa a nova planilha que vamos criar para armazenar os dados formatados.
   wsDinamica: Representa a nova planilha que conterá a Tabela Dinâmica.
   ultimaLinha: Usada para encontrar a última linha com dados na planilha.
   TabelaDinamica: Armazena a Tabela Dinâmica que vamos criar.
   intervaloDados: Contém o intervalo de dados que precisamos para a Tabela Dinâmica.
2. Abrindo o Arquivo e Configurando Planilhas:
   Set wbOriginal = Workbooks("Empresa A - Relatório Original.xlsx")
Set wsModelo = wbOriginal.Sheets("Modelo do Sistema")
   
   Aqui, estamos abrindo o arquivo chamado “Empresa A – Relatório Original.xlsx” e selecionando a planilha chamada “Modelo do Sistema” para usar seus dados.
3. Criando uma Nova Planilha:
   Set wsFormatado = wbOriginal.Sheets.Add(After:=wbOriginal.Sheets(wbOriginal.Sheets.Count)) wsFormatado.Name = "Empresa A - Formatado"
   
   Estamos criando uma nova planilha chamada “Empresa A – Formatado”, que será usada para armazenar os dados formatados.
4. Copia os Dados da Planilha Original:
   ultimaLinha = wsModelo.Cells(wsModelo.Rows.Count, "B").End(xlUp).Row wsModelo.Range("B3:B" & ultimaLinha).Copy wsFormatado.Range("A2")
   
   Neste trecho, encontramos a última linha com dados na coluna B (usando a contagem de linhas do Excel) e copiamos os dados de B3 até a última linha encontrada, colando na nova planilha a partir da célula A2. O mesmo processo se repete para outras colunas relevantes.
5. Removendo a Primeira Linha:
   wsFormatado.Rows(1).Delete
   
   Depois de copiar os dados, removemos a primeira linha da nova planilha, pois pode conter cabeçalhos ou informações indesejadas que não precisamos.
6. Selecionando Dados para a Tabela Dinâmica:
   Set intervaloDados = wsFormatado.Range("A1:G" & ultimaLinha)
   
   Definimos um intervalo de dados, que inclui todas as informações copiadas nas colunas de A a G até a última linha.
7. Criando a Tabela Dinâmica:
   Set wsDinamica = wbOriginal.Sheets.Add(After:=wbOriginal.Sheets(wbOriginal.Sheets.Count)) wsDinamica.Name = "Dinâmica" Set TabelaDinamica = wsDinamica.PivotTableWizard(SourceType:=xlDatabase, SourceData:=intervaloDados, TableDestination:=wsDinamica.Range("A1"))
   
   Criamos uma nova planilha chamada “Dinâmica” para armazenar a Tabela Dinâmica e, em seguida, usamos a função PivotTableWizard para gerar a Tabela Dinâmica a partir do intervalo de dados que definimos.
8. Organizando os Campos da Tabela Dinâmica:
   With TabelaDinamica .PivotFields("Equipe").Orientation = xlRowField .PivotFields("Valor").Orientation = xlDataField ' Configurações adicionais
End With
   
   Nesta parte, organizamos como a Tabela Dinâmica aparecerá. Adicionamos campos como “Equipe”, “Cod_Equipe”, e “Valor” aos seus respectivos lugares dentro da Tabela Dinâmica (linhas e valores).
9. Formatando a Tabela Dinâmica:
   .TableStyle2 = "TableStyleLight16" ' Estilo mais claro
   
   Ajustamos o estilo da Tabela Dinâmica para que ela tenha uma aparência mais clara e fácil de ler.
10. Exibindo uma Mensagem de Confirmação:
    MsgBox "Formatação e criação da Tabela Dinâmica concluídas com sucesso!"
    
    Por fim, mostramos uma mensagem dizendo que o processo foi concluído com sucesso.

Resumo da explicação

Esse código VBA realiza várias etapas para copiar dados de uma planilha, organizá-los em uma nova planilha/aba, e depois criar uma Tabela Dinâmica a partir desses dados, mas mantém os dados originais que foram extraídos do sistema para futura consulta. A estrutura do código é modular, onde cada passo é realizado sequencialmente para garantir que os dados sejam corretamente preparados e apresentados na Tabela Dinâmica.

Com a tabela dinâmica o profissional pode copiar e colar os dados gerados e alimentar a base de dados OU alongar o código VBA para que os dados da dinâmica sejam copiados e colados na base de dados automaticamente após 01 clique. Deixarei esse último código apartado no final da postagem.

Código completo para formatação:

Sub FormatacaoRelatorio()
Dim wbOriginal As Workbook
Dim wsModelo As Worksheet
Dim wsFormatado As Worksheet
Dim wsDinamica As Worksheet
Dim ultimaLinha As Long
Dim TabelaDinamica As PivotTable
Dim intervaloDados As Range

' Abre o arquivo original
Set wbOriginal = Workbooks("Empresa A - Relatório Original.xlsx")
Set wsModelo = wbOriginal.Sheets("Modelo do Sistema")

' Cria uma nova planilha chamada "Empresa A - Formatado"
Set wsFormatado = wbOriginal.Sheets.Add(After:=wbOriginal.Sheets(wbOriginal.Sheets.Count))
wsFormatado.Name = "Empresa A - Formatado"

' Encontra a última linha da planilha Modelo do Sistema
ultimaLinha = wsModelo.Cells(wsModelo.Rows.Count, "B").End(xlUp).Row

' Copia os dados da planilha Modelo do Sistema para a nova planilha
wsModelo.Range("B3:B" & ultimaLinha).Copy wsFormatado.Range("A2")
wsModelo.Range("C3:C" & ultimaLinha).Copy wsFormatado.Range("B2")
wsModelo.Range("D3:D" & ultimaLinha).Copy wsFormatado.Range("C2")
wsModelo.Range("A3:A" & ultimaLinha).Copy wsFormatado.Range("D2")
wsModelo.Range("F3:F" & ultimaLinha).Copy wsFormatado.Range("E2")
wsModelo.Range("G3:G" & ultimaLinha).Copy wsFormatado.Range("F2")
wsModelo.Range("E3:E" & ultimaLinha).Copy wsFormatado.Range("G2")

' Apaga a linha 1 da planilha "Empresa A - Formatado"
wsFormatado.Rows(1).Delete

' Define a última linha após a deleção da linha 1
ultimaLinha = wsFormatado.Cells(wsFormatado.Rows.Count, "A").End(xlUp).Row

' Selecione os dados de A a G até a última linha com valores
Set intervaloDados = wsFormatado.Range("A1:G" & ultimaLinha) ' A1:G é o intervalo selecionado após a exclusão da linha

' Cria uma nova planilha chamada "Dinâmica"
Set wsDinamica = wbOriginal.Sheets.Add(After:=wbOriginal.Sheets(wbOriginal.Sheets.Count))
wsDinamica.Name = "Dinâmica"

' Cria a Tabela Dinâmica
Set TabelaDinamica = wsDinamica.PivotTableWizard(SourceType:=xlDatabase, SourceData:=intervaloDados, TableDestination:=wsDinamica.Range("A1"))

' Organiza a Tabela Dinâmica conforme as instruções
With TabelaDinamica
    .PivotFields("Equipe").Orientation = xlRowField
    .PivotFields("Cod_Equipe").Orientation = xlRowField
    .PivotFields("Profissional").Orientation = xlRowField
    .PivotFields("Empresa").Orientation = xlRowField
    .PivotFields("Mês/Ano").Orientation = xlRowField
    .PivotFields("Valor").Orientation = xlDataField

    ' Altera o layout para Tabela e remove subtotais
    .TableStyle2 = "TableStyleLight16" ' Estilo mais claro
    .RowAxisLayout xlTabularRow
    .PivotFields("Equipe").Subtotals(1) = False
    .PivotFields("Cod_Equipe").Subtotals(1) = False
    .PivotFields("Profissional").Subtotals(1) = False
    .PivotFields("Empresa").Subtotals(1) = False
    .PivotFields("Mês/Ano").Subtotals(1) = False

    ' Configuração para "Repetir Rótulos"
    .PivotFields("Equipe").RepeatLabels = True
    .PivotFields("Cod_Equipe").RepeatLabels = True
    .PivotFields("Profissional").RepeatLabels = True
    .PivotFields("Empresa").RepeatLabels = True
    .PivotFields("Mês/Ano").RepeatLabels = True
End With

MsgBox "Formatação e criação da Tabela Dinâmica concluídas com sucesso!"
End Sub

Incluir o copia e cola para a base de dados

Sub CopiarDadosTabelaDinamica()
    Dim wbDestino As Workbook
    Dim wsFonte As Worksheet
    Dim wsDestino As Worksheet
    Dim ultimaLinha As Long
    
    ' Define a planilha de origem (Tabela Dinâmica)
    Set wsFonte = ThisWorkbook.Sheets("Dinâmica")
    
    ' Abre o arquivo "Exemplo_Post"
    On Error Resume Next
    Set wbDestino = Workbooks("Exemplo_Post.xlsx") ' Substitua pela extensão correta se necessário
    On Error GoTo 0
    
    If wbDestino Is Nothing Then
        MsgBox "O arquivo 'Exemplo_Post.xlsx' não está aberto.", vbExclamation
        Exit Sub
    End If
    
    ' Define a planilha de destino (Base dados)
    Set wsDestino = wbDestino.Sheets("Base dados")
    
    ' Encontra a próxima linha em branco na coluna A da planilha de destino
    ultimaLinha = wsDestino.Cells(wsDestino.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Copia os dados da Tabela Dinâmica (A3:F10)
    wsFonte.Range("A3:F10").Copy
    
    ' Cola os dados na próxima linha em branco da planilha de destino
    wsDestino.Range("A" & ultimaLinha).PasteSpecial Paste:=xlPasteValues
    
    ' Limpa a área de transferência
    Application.CutCopyMode = False
    
    MsgBox "Dados copiados com sucesso para 'Exemplo_Post', na planilha 'Base dados'."
End Sub

Atenção, os códigos acima precisam ser adaptados para a sua realidade. Se quiser concatenar é possível também, leia com atenção os códigos e respeite a lógica das ações. É importante que você valide os números. Neste exemplo a dinâmica feita pelo “robô” resultou em 143mil realizados na empresa A em jan/25 e este valor bate com o que foi demonstrado no relatório interativo e no controle inicial da área de negócio.

Você pode salvar esse código em um arquivo apartado na extensão xlsm, que é o excel habilitado para macros, e rodar essa ação independente do relatório. Isso concede eficiência para o robô já que o arquivo não levará consigo a carga do relatório em si (vai apenas processar).

Obrigada,
KHASHIMOTO