Consolidar: Várias Guias em uma Única - com VBA | Excelmax Soluções e Controles

segunda-feira, abril 28, 2014

Consolidar: Várias Guias em uma Única - com VBA



Consolidar: Várias Guias em uma Única - com VBA

guias, excel, excelmax, vba, concolidar

Recebi um e-mail de um amigo a quem não vejo há um bom tempo: Nadir Bassegio. O mesmo solicitou o desenvolvimento de uma solução para uma necessidade sua. A seguir, a questão levantada no e-mail que recebi:




"A questão é a seguinte: gero um relatório no ERP que vai diretamente para o Excel. Ele gera 40 guias (planilhas) numa pasta do Excel, todas com o mesmo nº de colunas, porém com quantidade de linhas diferentes.
Estou usando excel 2007 e tentando juntar todas estas informações em uma única guia (planilha), usando o recurso ... (Aqui ele menciona o que tentou utilizar e não funcionou) ... "
Sua necessidade é: "Desenvolver uma solução para fazer esta junção, de todas as guias em uma única."

Para ajudar meu amigo Nadir e também outras pessoas que talvez precisem de uma solução igual ou parecida, resolvi escrever esta postagem que trata do assunto.


guias, excel, excelmax, vba, concolidar, planilha


Conforme a figura ao lado, estarei utilizando 3 planilhas (Plan1, Plan2 e Plan3).

As informações contidas nestas planilhas serão todas consolidadas na planilha Consolidado.



Para fazer a consolidação das informações, criei uma macro. Observe a figura a seguir.
guias, excel, excelmax, vba, concolidar, planilha, macro

Entendendo a Macro:

1ª linha: limpa a área na planilha 1 (Consolidados) para garantir que não haja dados inseridos anteriormente.

2ª linha: com a variável 'plans', faz uma contagem da quantidade de planilhas existente na pasta (em nosso exemplo há 4 planilhas).


A instrução 'For...Next' faz uma repetição do grupo de instruções um número específico de vezes.

Em nosso caso, fará a repetição das instruções (que estão entre For...Next) por três vezes. A instrução 'For n = 2 to plans' faz 3 repetições das instruções pois inicia em 2 até atingir a valor da variável plans que é igual a 4 (quantidade de planilhas da pasta de trabalho).

As instruções que são repetidas (por 3 vezes em 'For...Next') em todas as linhas com as informações (até encontrar uma célula vazia na coluna 'A') de cada planilha incluindo as mesmas na planilha Consolidados (Sheets(1)).

A quantidade de planilhas (guias) pode ser variável desde que tenham o mesmo número de colunas com os mesmos tipos de informação em cada coluna. Isso ocorre porque, como vimos, a variável 'plans' fará a contagem da quantidade de planilhas existentes na pasta de trabalho e as instruções serão repetidas o número de vezes até atingirem esta quantidade.

Espero ter sido claro e espero que esta postagem possa estar respondendo a questão mencionada.

Se desejarem divulgar a seus amigos, fique a vontade e eu ficarei grato pois, muitos podem estar precisando desta solução ou algo parecido que pode ser atendido com algum ajuste.

Até a próxima postagem!
Ivair Claudio Ferrari



28 comentários:

  1. Olá Professor.

    Esta postagem está simplesmente fantástica. Tinha um procedimento aqui que era muito demorado mas, agora com esta macro, ficou muito bom ... rápido e com exatidão.

    Parabéns !!!
    Solano.

    ResponderExcluir
  2. O Mestre Ivair, show de bola é isto daí mesmo, quem sabe sabe...

    Obrigado

    ResponderExcluir
  3. Valeu Nadir! Espero que ajude.

    Precisando, estou a disposição

    Ivair

    ResponderExcluir
  4. Deste jeito não tem graça. Assim fica muito fácil de fazer as coisas do dia-a-dia.

    Parabéns !!!!!!!!!!!!

    ResponderExcluir
  5. Concordo com o Vander!

    Não me dou muito bem com o Inglês mas, ficou fácil agora.....

    ResponderExcluir
  6. Parabéns pelo seu blog. Eu e meus amigos somos fãs. Continue nos ensinando. Obrigada!

    ResponderExcluir
  7. Parabéns Ivair! São ótimos seus métodos de ensino...

    ResponderExcluir
  8. Obrigado a todos pelos comentários. Continuem acessando e divulguem a seus amigos pois, eles podem estar precisando...

    Grande abraço a todos!
    Professor Ivair Claudio Ferrari.

    ResponderExcluir
  9. Muito Obrigada Ivair, isso vai me ajudar muito! Agora complicando um pouquinho, como faço isso pegando dados de diversas planilhas? Existe uma maneira?

    Obrigada!

    Fernanda

    ResponderExcluir
  10. Boa tarde!


    Essa macro funcionou com a utilização do Excel de 60 abas em dois arquivos diferentes, sendo que cada aba possui o nome diferente.

    bom trabalho, grato

    ResponderExcluir
    Respostas
    1. Bom ter ajudado.

      Grande abraço!
      Professor e consultor: Ivair Claudio Ferrari

      Excluir
  11. Amigo, vc está de parabéns!!! Tava quebrando a cabeça para unificar n abas, e sua macro é simples e funcional!!!! Ou seja, perfeito!!!!

    ResponderExcluir
  12. Respostas
    1. Obrigado amigo Tiago! Acompanhe pois em breve novas e ricas matérias serão publicadas aqui no Excelmax Soluções.

      Grande abraço!

      Excluir
  13. Cara! Parabéns
    Salvou minha vida.

    Abraço

    ResponderExcluir
  14. Boa noite professor,

    Tenho uma planilha com 200 guias todas com a mesma qd de colunas e linhas porém possuem
    cabeçalho e com essa macro não estou conseguindo consolidar as guias em um só relatório, poderia me ajudar?
    Ficarei muito grato

    Jones

    ResponderExcluir
    Respostas
    1. Olá amigo Jones!
      Sugiro que envie a planilha e meu e-mail que ajusto. (ivairferrari@gmail.com)

      Atenciosamente!
      Ivair Claudio Ferrari

      Excluir
  15. Olá tudo bem
    No meu caso eu tenho 8 guias no mesmo arquivo, quero criar uma 9 (nona) guia ao qual une todas as outras em uma tabela só!

    danielsistemas2016@hotmail,com

    Desde já agradeço pela atenção.

    ResponderExcluir
    Respostas
    1. Olá Leonardo! Pelo que entendi, é só colocar o código em sua planilha e rodar. Ele conta o nr de planilhas. Com isso, não importa o nr de guias que o seu arquivo contêm.

      Excluir
  16. Boa tarde, utilizei a sua própria macro, e deu certo. No entanto gostaria de adicionar colunas, a sua planilha base tras 5 colunas, enquanto eu preciso trazer 9 colunas. Qual item devo modificar em sua macro?

    ResponderExcluir
  17. Boa tarde, gostei muito do seu post, e arquivo base. No entanto gostaria de saber qual a linha da macro que deveria editar para me trazer um número maior de colunas. No seu arquivo o consolidar me trás 5 colunas, no entanto preciso trazer 9 colunas. Muito obrigada e aguardo retorno

    ResponderExcluir
    Respostas
    1. Boa tarde Antonia!
      Você deve acrescentar 4 linhas a partir da linha:
      Sheets(1).Cells(linha, 5) = Sheets(n).Cells(lin, 5), alterando os números 5, para 6, 7, 8 e 9.

      As linhas seguintes podem ser excluídas:
      Sheets(1).Cells(linha, 7).Font.ColorIndex = n + 1
      Sheets(1).Cells(linha, 7) = Sheets(n).Name

      Excluir
  18. em minha planilha tenho varias abas, queria saber se tem como selecionar somente algumas abas para promover essa consolidação

    ResponderExcluir
    Respostas
    1. Olá! É possível sim. Para fazer isso, deve-se encontrar uma maneira para que o Excel selecione as abas a serem consolidadas. Caso queira ver uma matéria publicada, explicando isto, envie uma planilha exemplo para o email: ivairferrari@gmail.com.

      Excluir
  19. Muito bacana sua postagem, obrigado! Se for possível tirar uma dúvida eu agradeço, pq é necessário criar duas variáveis "linha" e "lin"?

    ResponderExcluir
    Respostas
    1. Olá Celso!
      Cada vez que é executado um Lop (Do Until ... Loop) é preciso aumentar o valor para cada variável em 1 para que ele grave a informação na próxima linha.

      Excluir

Seja um participante desta comunidade !
Deixe aqui seu comentário e/ou sugestão.
Obrigado !

Ivair Claudio Ferrari