Consolidação de dados em Excel VBA – União de dados em Excel VBA

Identificação de máquinas online - Ping em Ip Excel Servidores
Identificação de máquinas online – Ping em Ip Excel Servidores
15 de agosto de 2012
Planilha de Controle de Horas de Terceiros - Excel
Planilha de Controle de Horas de Terceiros – Excel
26 de agosto de 2012

Consolidação de dados em Excel VBA – União de dados em Excel VBA

Objetivo: Esta planilha realiza a consolidação de dados de várias planilhas a partir de uma configuração inicial. Muito útil para unir dados de filiais.

É muito comum a necessidade de unir dados de várias planilhas, principalmente quando trabalhamos em empresas com filiais.

Estas informações normalmente tem que ser consolidadas manualmente frequentemente o que gera um bom trabalho.

Nesta planilha automatizada com o uso de Excel VBA você tem um modo de como fazer com que estes dados sejam unidos automaticamente.

Como configurar

Para configurar a planilha altere os dados da planilha:

Caminho: Digite o caminho completo do arquivo, incluindo a extensão

Planilha: Digite o nome da planilha se for apenas uma planilha do arquivo, se forem todas as planilhas do arquivo que devam ser unidas, não digite nada neste campo

Coluna final: Digite a coluna até qual a planilha Excel deverá realizar a união dos dados.

Altere também na planilha Consolidado o nome dos campos conforme as planilhas que você irá unir.

Assim que forem realizadas todas as alterações necessárias uma única vez, basta você clicar no botão que a planilha irá realizar a consolidação de todos os dados no Excel.

Código VBA Excel

Abaixo o código VBA utilizado nesta planilha.

Sub lsConsolidarPlanilhas()
    Dim lWorkbook           As Workbooks
    Dim lWorksheet          As Worksheet
    Dim lUltimaLinhaAtiva   As Long
    Dim lControle           As Long
    Dim lUltimaLinhaAtiva2  As Long
    Dim lUltimaLinhaAtiva3  As Long
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Worksheets("Consolidado").Select
    Worksheets("Consolidado").Range("A2:G1000000").Select
    Selection.ClearContents
    
    
    lUltimaLinhaAtiva = Worksheets("Configuração").Cells(Worksheets("Configuração").Rows.Count, 1).End(xlUp).Row
    lControle = 2
    
    While lControle <= lUltimaLinhaAtiva
        Workbooks.Open Filename:=Worksheets("Configuração").Range("A" & lControle).Value
            
        Set lworkbooks = ActiveWorkbook
            
        For Each lWorksheet In lworkbooks.Worksheets
            If (lWorksheet.Name = Workbooks("Consolidado.xlsm").Worksheets("Configuração").Range("B" & lControle).Value Or _
                Workbooks("Consolidado.xlsm").Worksheets("Configuração").Range("B" & lControle).Value = "") Then
                
                Workbooks(lworkbooks.Name).Worksheets(lWorksheet.Name).Activate
                lUltimaLinhaAtiva2 = Worksheets(lWorksheet.Name).Cells(Worksheets(lWorksheet.Name).Rows.Count, 1).End(xlUp).Row
                lWorksheet.Select
                lWorksheet.Range("A2:" & Workbooks("Consolidado.xlsm").Worksheets("Configuração").Range("C" & lControle).Value & lUltimaLinhaAtiva2).Select
                Selection.Copy
                
                lUltimaLinhaAtiva3 = Workbooks("Consolidado.xlsm").Worksheets("Consolidado").Cells(Workbooks("Consolidado.xlsm").Worksheets("Consolidado").Rows.Count, 1).End(xlUp).Row + 1
                Workbooks("Consolidado.xlsm").Worksheets("Consolidado").Activate
                Workbooks("Consolidado.xlsm").Worksheets("Consolidado").Range("A" & lUltimaLinhaAtiva3).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            End If
        Next lWorksheet
        
        Workbooks(lworkbooks.Name).Close
        
        lControle = lControle + 1
    Wend
    
    Worksheets("Configuração").Select
    Worksheets("Configuração").Range("A1").Select
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox "Planilhas consolidadas!"
        
End Sub

A planilha principal é a Consolidado.xlsm, as outras em anexo são para você usar como exemplo, não esqueça de salvá-las no seu computador e configurar o local aonde foram salvas.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

40 Comentários

  1. Myrian disse:

    Oi Marcos,
    Tentei fazer a consolidação mas deu erro:
    Erro em tempo de execução 9
    Subscrito fora do intervalo
    Depurado.
    If (lWorksheet.Name = Workbooks(“Consolidado.xlsm”).Worksheets(“Configuração”).Range(“B” & lControle).Value Or _
    Workbooks(“Consolidado.xlsm”).Worksheets(“Configuração”).Range(“B” & lControle).Value = “”) Then

  2. Gerson disse:

    Perfeito, me ajudou muito.

  3. cladeson disse:

    Bom dia, achei muito interessante este arquivo era o que precisava, porem trabalho com planilhas compartilhadas e grandes com várias colunas, como também tem login de entrar, ao inserir o nome da pasta o excel fecha o que fazer?

  4. cladeson disse:

    bom dia Marcos!

    obrigado pela dica.

    Porem ainda continuo com problemas com a minha planilha, ou ela fecha o excel, ou ela informa que consolidou, porem não copia os dados.
    Consigo fazer com que ela consolide as informações em geral, porem ao inserir qual neme da Aba ela fecha e ou não copia os dados. já tentei muitas coisas, verificar nome está errado ou diferente, se o caminho está diferente, até mesmo reorganização das abas. minha planiha não está protegida por senhas, apenas são compartilhadas e as abas ficam ocultas. ok fazer. como faço para te enviar o modelo q estou elaborando.

  5. Alexandre disse:

    Boa tarde Marcos,

    Parabéns pela planilha. Testei e achei bem legal, mas eu precisava de uma que some os dados de diversas planilhas (arquivos excel de filiais diferentes mas no mesmo formato) e gere uma planilha consolidada. Qual seria a mudança a ser feita em relação ao codigo VBA desta planilha ?
    obrigado

  6. Aline Martins disse:

    Baixei a planilha acima e alterei o caminho, planilha e coluna como segue. Também alterei as planilhas na aba “consolidado”.
    Só que a mesma não está consolidando devido a um ERRO:
    Erro em tempo de execução ’1004′:
    O Excel não pode acessar ’2013′. O documento pode ser somente leitura ou estar criptografado.

  7. Aline Martins disse:

    O erro acima eu consegui arrumar, porém agora tenho outro “problema”.
    Indiquei os caminhos, planilhas e colunas e ele consolida. Na hora que abro o consolidado, verifico que somente parte foi consolidado.

    Pode me ajudar?

    • Marcos Rieper disse:

      Bom dia Aline,

      Qual o tamanho das suas planilhas? Já tive este problema também, infelizmente é uma falha do próprio Excel em não conseguir transpor certa quantidade de dados.

      Abraço

      Marcos Rieper

  8. cladeson disse:

    Bom dia Aline, com relação ao seu problema acima, se seu arquivo principal estiver filtrado, o consolidado copiará somente ele, então antes de solcitar a consolidação certifique q a planilha original não contenha algum filtro. ok.

  9. Olá,

    o que é necessário modificar para que possam ser preenchidas apenas 2 colunas da planilha?

    por exemplo: tenho uma planilha com as colunas: A, B, C e D

    e na consolidada eu quero que puxe só a C e D…

    Existe essa possibilidade?

    Abraço e ótimo trabalho!

  10. Boa tarde Marcos,

    Então…Desculpa o abuso ta?! sua planilha é ótima, com certeza vai me ajudar e muito em algumas tarefas, mas deixa eu te perguntar…

    É que assim, eu utilizarei ela para consolidaçao de várias planilhas, só que de todas essas planilhas, eu preciso de apenas 2 colunas que é onde contém a informação que eu realmente necessito.

    Eu precisaria que fossem inseridas colunas ao invés de linhas pelo fato de facilitar a visualização e também pq a nossa planilha que alimentamos já funciona assim, ai seria uma “mão na roda”…rs

    Eu tentei modificar o código, mas não deu certo…

    Caso vc tenha alguma dica ou sugestão seria muito bem vinda…

    Abração!

  11. Ysabela disse:

    Boa noite!

    Marcos,

    Tenho uma planilha com várias guias, gostaria de consolidar automaticamente os dados dessas guias numa guia mãe para que eu possa gerar relatórios.

    Isto é existem várias planilhas (1,2,3,4,5) onde são lançados os mesmos grupo de dados, gostaria que automaticamente sempre que eu lançar um dado na planilha 1,2,3,4 ou 5 esses dados sejam direcionados também para a planilha 6 para que eu possa gerar relatórios.

    Tem algum tutorial para me ajudar.

    Desde Já agradeço!

    Abraço

  12. dadoap disse:

    Olá,estou tentando usar a planilha para consolidar outras 25 planilhas, mas estas 25 planilhas contém vínculos, e sempre que o consolidado busca as informações das planilhas, ele pergunta se desejo atualizar ou não os vínculos.

    Sabe se tem alguma maneira de alterar a programação para a resposta desta pergunta ser sempre “Sim”?

    • dadoap disse:

      Também gostaria de saber se há um modo de formatar os dados consolidados automaticamente em uma tabela, pois quero gerar algumas tabelas dinâmicas a partir destes dados, e quero que a fonte das dinâmicas se atualize automaticamente quando consolidar os dados. Obrigado!!

    • dadoap disse:

      Outra coisa, é impressão minha ou do jeito que o código está formatado ele só limpa das colunas A até G do Consolidado? Por que isso?

    • Marcos Rieper disse:

      Boa noite Eduardo,

      Você pode colocar o código Application.DisplayAlerts = False antes do código e depois colocar Application.DisplayAlerts = True no final.

      Abraço

      Marcos Rieper

  13. welton disse:

    Ola Marcos… Fiz tudo que pede mas quando vou consolidar da um erro
    Erro no tempo de execução
    Subscrito fora do intervalo.
    O que sera que estou fazendo de errado…

  14. vaN disse:

    Otima planilha era o que eu precisava!
    Muiitissimo Obrigada!!

  15. Excelente macro, simples, fácil e funciona. Perfeito, Muito Obrigado!!!!

  16. Daniel disse:

    Marcos,

    Sua planilha me ajudou muito… muito obrigado!

    Só gostaria de um HELP, quando eu trago os dados das planilhas, como faço pra trazer a formatação por ex: a cor da célula, é possível??

    abraços

  17. Cruz disse:

    Boa tarde. Sua planilha me atende em 100%, mas gostaria de tirar uma dúvida.
    Na minha planilha, os dados começam a contar da 4ª linha, ou seja, as 3 primeiras são para o cabeçalho. Fiz essa alteração:
    lControle = 4
    Mas dessa forma, quando executa a macro, os cabeçalhos das outras planilhas também são carregados e eu tenho que excluir essas informações com filtro.
    Por favor, poderia me orientar onde devo alterar para carregar as outras planilhas também da 4ª linha?

    Muito obrigado e parabéns.

    • Marcos Rieper disse:

      Boa tarde Jonas,

      Teria que ver a sua planilha, mas para fazer esta alteração teria que alterar a programação VBA da planilha. Pode enviar sua dúvida em nosso fórum com a planilha em anexo.

      Abraço

      Marcos Rieper

  18. Cruz disse:

    Infelizmente não consegui postar no forum.
    A única mudança que fiz nas planilhas A e B foi inserir 2 linhas no topo, fazendo a 1ª linha de título da planilha passar para 3ª linha.

    • Marcos Rieper disse:

      Bom dia Jonas,

      Talvez este seja o problema de inserir as linhas, principalmente se as primeiras linhas estiverem mescladas.

      Tente por favor novamente no fórum, acho que você já está cadastrado, e mande a sua planilha para verificarmos.

      Abraço

      Marcos Rieper

  19. Cruz disse:

    Boa tarde.
    Eu tento entrar no forum com esse mesmo usuario “Cruz” e informa que está inativo. No email que recebi automaticamente, não tem o link para que eu possa confirmar o email. Por favor, pode ajudar?

    • Marcos Rieper disse:

      Boa tarde Jonas,

      Obrigado pelo seu contato.

      Por causa de problemas com spam bots, que fizeram com que o fórum anterior ficasse inutilizado, formos obrigados a criar uma validação para autorizar as pessoas a se cadastrarem, infelizmente.

      Mas fazemos o possível para avaliar os novos cadastros e habilitar sempre o mais rápido possível.

      Abraço

      Marcos Rieper

  20. Camila disse:

    A macro parece excelente, mas não consegui utilizar porque aparece o erro: 1004 o documento pode ser somente leitura ou estar criptografado. Entretanto não é o caso de nenhuma das planilhas.

  21. Bruno disse:

    Tentei usar a planilha para consolidar dados de outras que estão armazenadas em um local de rede e dá o seguinte erro:

    Erro de tempo de execução 1004

    O Microsoft Excel não pode acessar o arquivo “endereço do arquivo”

  22. Bruno disse:

    Não estou conseguindo usar a planilha para arquivos salvos em locais de rede. Alguma sugestão?

  23. Adriano disse:

    Marcos bom dia, funcionou perfeitamente. Teria como incluir na base de dados uma coluna com o nome da planilha que colou? Como se torna uma única base de dados eu precisaria diferenciar a planilha que colou para criar mais um campo.
    Um exemplo colou as colunas ABC das Plan1 a Plan10. Na coluna D teria como aparecer o nome da planilha para saber a qual planilha aquela linha se refere
    ABC D
    XXX Plan1
    XXX Plan1
    XXX Plan2
    XXX Plan2

    Um grande abraço.

  24. Elton disse:

    Muito bem! Funcionou 100%. Muito obrigado!

  25. Ricardo Sousa disse:

    Parabéns pelo Excelente trabalho prestado Marcos !

    Aproveitando se oportuno for, a alguma maneira de realizar uma pesquisa para gravação de dados.
    Ex.: Tenho 02 ou 03 planilhas (Planilhas “filhas”) que alimentam a base de dados (Planilha “mãe”), com dados a principio diferentes (digo a principio por que são alimentadas sem validação de dados inicialmente).
    No momento do registro existe código para verificar se um “dado” na coluna de uma das “filhas” já existir na “mãe” ele ignorar esse novo registro ?

    Reforço a parabenização pelo Excelente trabalho realizado e compartilhado.

    Ricardo Sousa

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.