Unir planilhas Excel com VBA

Análise de indicadores no Excel com o recurso Câmera e uso de VBA
Análise de indicadores no Excel com o recurso Câmera e uso de VBA
1 de julho de 2015
Criar arquivo de texto formatado Excel
Excel VBA – Criar arquivo de texto delimitados
9 de julho de 2015

Unir planilhas Excel com VBA

Unificar planilhas Excel com VBA

Neste artigo apresentaremos um código VBA Excel que realiza a unificação de planilhas.

Imagine a seguinte situação. Você tem várias pastas de trabalho Excel preenchidas por diversos usuários, ou ainda vieram dezenas de arquivos referentes á um mês de processamento de arquivos de um determinado fornecedor e você gostaria de uni-los para analisá-los.

Este código VBA Excel realiza a unificação das planilhas que estiverem abertas em um único arquivo informado no início do processo.

Abaixo o processo como funciona:

1. Copie o código VBA e adicione á sua pasta Personal. Veja aqui como realizar esta tarefa: Habilitando a guia desenvolvedor e copiando códigos VBA da internet.

'UnificarPlanilhas Macro
Sub lsUnificarPlanilhas()

  Dim Message As String
  Dim Title As String
  Dim Default As String
  Dim MyValue As String
  Dim wb As Workbook
  Dim lUltimaColunaAtiva As Long
  Dim lUltimaLinhaAtiva As Long
  Dim lRng As Range
  
  Message = "Informe o nome da Planilha de Destino"
  Title = "Unificar Planilhas"
  Default = "1"
 
  'Solicita informação da planilha que será unificada
  PlanilhaDestino = InputBox(Message, Title, Padrão)
  If PlanilhaDestino = "" Then
     MsgBox "Planilha não informada, a macro será Finalizada!"
     Exit Sub
  End If
 
  'Verificar se o arquivo existe
  For i = 1 To Workbooks.Count
      If Workbooks(i).Name = PlanilhaDestino Then
          Exit For
      Else
        If i = Workbooks.Count Then
          MsgBox "Planilha Destino não Encontrada, a macro será Finalizada!"
          Exit Sub
        End If
      
      End If
  Next i
  
  'Colar os dados selecinoados
  For i = 1 To Workbooks.Count
       If Workbooks(i).Name <> "PERSONAL.XLSB" And Workbooks(i).Name <> PlanilhaDestino Then
            Workbooks(Workbooks(i).Name).Worksheets(1).Activate
            
            lUltimaLinhaAtiva = Cells(Rows.Count, 1).End(xlUp).Row
            lUltimaColunaAtiva = ActiveSheet.Cells(1, 5000).End(xlToLeft).Column
            Set lRng = Range(Cells(1, lUltimaColunaAtiva).Address)
            
            Range("A" & 2 & ":" & gfLetraColuna(lRng) & lUltimaLinhaAtiva).Select
            Selection.Copy
            
            Workbooks(PlanilhaDestino).Worksheets(1).Activate
            
            lUltimaLinhaAtiva = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Range("A" & lUltimaLinhaAtiva).Select
            
            ActiveSheet.Paste
            Application.CutCopyMode = False
      End If
          
  Next i
  ' Fecha planilhas unificadas
  For Each wb In Application.Workbooks
    If wb.Name <> "PERSONAL.XLSB" And wb.Name <> PlanilhaDestino Then
        wb.Close SaveChanges:=False
    End If
  Next
 
End Sub

Function gfLetraColuna(ByVal rng As Range) As String
    Dim lTexto() As String
    
    lTexto = Split(rng.Address, "$")
    
    gfLetraColuna = lTexto(1)
End Function

2. Abra todas as planilhas que gostaria de unificar;
3. Vá na guia Desenvolvedor e no botão Macros, selecione PERSONAL.XLSB!lsUnificarPanilhas e clique em Executar;

Unificar planilhas Excel com VBA

4. Informe a planilha que receberá a união das planilhas e clique em OK;

Unificar planilhas Excel com VBA

5. Aguarde o término do processamento. Todos os dados da primeira planilha ativa de cada arquivo serão copiados e as planilhas fechadas, exceto a planilha de destino.

Unificar planilhas Excel com VBA

Se quiser pode treinar com as planilhas utilizadas neste exemplo efetuando o download no botão abaixo.

DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS:

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. Bigo disse:

    Bom dia, essa macro me ajudou muito, entretanto preciso dela para planilhas que possuem fórmulas. Como ajustaria essa macro para colar valores na planilha que unificará?

    Agradeço desde já.

  2. Bom dia, muito obrigado pela macro, foi muito útil, mas como faço quando a planilha tem varias abas?

    • Gabriella disse:

      Olá, bom dia,
      Também estou com a mesma situação, preciso unificar várias abas de uma mesma planilha. Chegou a encontrar alguma solução Roniery?

  3. luis disse:

    Olá boas. A macro está espectacular, porém gostaria de saber se era possivel que os dados que tivessem o mesmo nome aparecessem na mesma linha e que somasse a quantidade, isto é: tenho 3 Planilhas de excel com os mesmos nomes de produtos mas com quantidades diferentes; queria que na planilha final os dados com o mesmo nome aparecessem na mesma linha e que na coluna das quantidades somasse as mesmas em vez de as duplicar que é o que acontece com esta macro.
    Obrigado.
    Luis

  4. leandra disse:

    Caro Marcos,

    Muito obrigada pelo post. Fiz o teste com os arquivos que você disponibilizou e deu certo. Também testei com meus dados.

    Eu gostaria de fazer uma mudança, mas não estou conseguindo o resultado esperado. Eu tenho vários arquivos com dados diferentes, as variáveis são distintas, mas os dados dizem respeito ao mesmo conjunto de atores. Então, os atores se repetem e são os mesmos em todos os arquivos. Assim, eu preciso unir os arquivos, mas não colando os dados a partir da última linha, mas sim a partir da última coluna.

    Muito obrigada pela ajuda.

    • Marcos Rieper disse:

      Olá Leandra,

      Neste caso a consulta é diferente, mas para fazer isso você precisará saber mais de SQL e usar JOIN.

      Outra solução é utilizar o PowerQuery do Excel e unir estes dados por esta ferramenta.

      Abraço
      Marcos Rieper

  5. Leandra disse:

    Bom dia Marcos,

    A macro é super útil e eu consegui rodar direitinho.

    Eu gostaria de fazer uma modificação pra que a partir de uma coluna semelhante entre os arquivos (“id”) os dados fossem colados horizontalmente, aumentando as colunas, pois as linhas são dados idênticos em cada arquivo.

    Fiz uma tentativa em mudar o que está em:
    lUltimaLinhaAtiva = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Range(“A” & lUltimaLinhaAtiva).Select

    Entretanto, não consegui o que queria. Você poderia, por favor, me auxiliar neste ponto?

    Muito obrigada,

  6. Pedro Espindola Neto disse:

    eu gostaria de tirar dúvidas em vba.

  7. Pedro Espindola Neto disse:

    Olá, Marcos Rieper, obrigado pela atenção.
    Minha dúvida é, tenho duas pastas conhecidas, a 1ª”Caderneta de cálculo” e a 2ª”Coordenadas”.
    Preciso unir uma quantidade n? de planilhas, exceto a primeira planilha(planilha mestra) e também não sei quantas planilhas irei editá-las; com essa união das planilhas, preciso copiá-las e colar na pasta”Coordenadas”, somente as colunas; A6 ,C6, L6, K6 e M6, a linha 5 é o cabeçalho.
    Sendo que a pasta “Caderneta de cálculo” também não sei quantas linhas serão preenchidas em cada planilha criada. Valeu Marcos, grato pela atenção.

  8. Pedro Espindola Neto disse:

    Olá, bom dia, Marcos.
    Queria tentat tirar dúvidas. Unir planilhas somente valores de uma pasta de trabalho e colar em outra pasta. só que a quantidade de planilhas a ser unidas, varia, e as planilhas que contem os valores, também varia de acordo com a edição em cada planilha. Grato pela ajuda.

  9. adel metzler disse:

    nossa! passo por aqui para deixar meu obrigado, funcionou perfeitamente para minha necessidade… juntei mais de 20 arquivos em poucos segundos (demoraria dezenas de minutos)

  10. André Santana disse:

    Olá, Marcos, parabéns pelo post, que continua relevante mesmo com o passar do tempo!
    Tentei implementar a rotina mas deu o seguinte erro: Erro em tempo de execução ‘1004’: Erro de definição de aplicativo ou de definição de objeto. Indo ao depurador, ele acusa o erro na seguinte linha do script
    lUltimaColunaAtiva = ActiveSheet.Cells(1, 5000).End(xlToLeft).Column
    O que fazer? Obrigado

  11. Elizeu Martins disse:

    Boa noite Marcos,

    Parabéns pelo seu arquivo é fantástico, porém eu infelizmente não consegui porque talvez eu fiz alguma coisa errada,
    Dá um erro de planilha não encontrada, sabe me dizer o que estou fazendo de errado?

    Obrigado

  12. Elizeu Martins disse:

    As planilhas que eu quero consolidar são 3 arquivo diferentes
    Planilha 1
    Planilha 2
    Planilha 3

    Esses arquivos estão na minha área de trabalho
    Fiz um outro arquivo Chamado Unir planilhas e colei a macro nele, porém quando rodo a macro ela não encontra os outros arquivos
    Consegue me ajudar?

    Obrigado

  13. Jefferson disse:

    estou tendo erro quando chega nesse ponto da macro na hora de execuçao
    If Workbooks(i).Name (“PERSONAL.XLSB”) And Workbooks(i).Name Planilha Destino Then
    sabe me dizer o q devo fazer
    aparece erro de sintaxe

  14. Leonardo disse:

    Olá Marcos, no meu caso não funcionou pq aparece um erros de compilação e de sintaxe no código If Workbooks(i).Name “PERSONAL.XLSB” And Workbooks(i).Name PlanilhaDestino Then

  15. Marco disse:

    o meu da erro em duas linhas apontando como se faltasse um then no final do if mas o Then está lá:

    If Workbooks(i).Name “PERSONAL.XLSB” And Workbooks(i).Name PlanilhaDestino Then

    If wb.Name “PERSONAL.XLSB” And wb.Name PlanilhaDestino Then

  16. Philipe disse:

    estou com o problema que essa parte da Macro está dando erro:

    If wb.Name “PERSONAL.XLSB” And wb.Name PlanilhaDestino Then

    como faço para consertar isso?

  17. Nilton disse:

    Na parte da macro:
    Next i
    ‘ Fecha planilhas unificadas
    For Each wb In Application.Workbooks
    If wb.Name “PERSONAL.XLSB” And wb.Name PlanilhaDestino Then
    wb.Close SaveChanges:=False
    End If
    Next

    Especificamente na linha : If wb.Name “PERSONAL.XLSB” And wb.Name PlanilhaDestino Then
    estou recebendo o seguinte erro:
    Erro de compilação
    Erro de sintaxe

    Alguém pode me ajudar?
    envie a resposta para meu e-mail (nosilva@gmail.com) por favor

  18. Felipe disse:

    Desconsidere o erro anterior, o erro esta presente nessa linha após “Fecha planilha unificada”

    If wb.Name “PERSONAL.XLSB” And wb.Name PlanilhaDestino Then

  19. Felipe disse:

    não está aparecendo o simbolo de “menor” e “maior” entra Name e planilha de destino

  20. Marcos Rieper disse:

    Olá Felipe,

    Foi corrigido no artigo, obrigado por avisar, faça assim: If wb.Name “PERSONAL.XLSB” And wb.Name PlanilhaDestino Then

  21. Felipe disse:

    Coloquei o sinal de “=” e macro executou normalmente, mas ocorreu outro erro.
    “Erro em tempo de execução ‘1004’:

    Ao clicar em depurar ele me aponta a seguinte linha:
    lUltimaColunaAtiva = ActiveSheet.Cells(1, 5000).End(xlToLeft).Column

  22. Bianca disse:

    Boa tarde,
    Estou copiando a macro, mas quando executada ela não encontra planilha de destino.
    Não sei o que posso estar fazendo errado…

    Se puder ajudar.

  23. Vilson José Moreira disse:

    Boa Tarde

    Tentei seguir seu exemplo e não consegui a unificação das planilhas, você pode ajudar-me?

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.