Consolidar: Várias Guias em uma Única - com VBA
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.
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.
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
Postagens
Olá Professor.
ResponderExcluirEsta 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.
O Mestre Ivair, show de bola é isto daí mesmo, quem sabe sabe...
ResponderExcluirObrigado
Valeu Nadir! Espero que ajude.
ResponderExcluirPrecisando, estou a disposição
Ivair
O melhor blog de Excel que conheço. muito bom!
ResponderExcluirDeste jeito não tem graça. Assim fica muito fácil de fazer as coisas do dia-a-dia.
ResponderExcluirParabéns !!!!!!!!!!!!
Concordo com o Vander!
ResponderExcluirNão me dou muito bem com o Inglês mas, ficou fácil agora.....
Parabéns pelo seu blog. Eu e meus amigos somos fãs. Continue nos ensinando. Obrigada!
ResponderExcluirParabéns Ivair! São ótimos seus métodos de ensino...
ResponderExcluirObrigado a todos pelos comentários. Continuem acessando e divulguem a seus amigos pois, eles podem estar precisando...
ResponderExcluirGrande abraço a todos!
Professor Ivair Claudio Ferrari.
Muito Obrigada Ivair, isso vai me ajudar muito! Agora complicando um pouquinho, como faço isso pegando dados de diversas planilhas? Existe uma maneira?
ResponderExcluirObrigada!
Fernanda
Boa tarde!
ResponderExcluirEssa 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
Bom ter ajudado.
ExcluirGrande abraço!
Professor e consultor: Ivair Claudio Ferrari
Amigo, vc está de parabéns!!! Tava quebrando a cabeça para unificar n abas, e sua macro é simples e funcional!!!! Ou seja, perfeito!!!!
ResponderExcluirMuito bom. parabéns pelo post.
ResponderExcluirObrigado amigo Tiago! Acompanhe pois em breve novas e ricas matérias serão publicadas aqui no Excelmax Soluções.
ExcluirGrande abraço!
Cara! Parabéns
ResponderExcluirSalvou minha vida.
Abraço
Boa noite professor,
ResponderExcluirTenho 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
Olá amigo Jones!
ExcluirSugiro que envie a planilha e meu e-mail que ajusto. (ivairferrari@gmail.com)
Atenciosamente!
Ivair Claudio Ferrari
Olá tudo bem
ResponderExcluirNo 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.
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.
ExcluirBoa 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?
ResponderExcluirBoa 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
ResponderExcluirBoa tarde Antonia!
ExcluirVocê 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
em minha planilha tenho varias abas, queria saber se tem como selecionar somente algumas abas para promover essa consolidação
ResponderExcluirOlá! É 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.
ExcluirMuito 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"?
ResponderExcluirOlá Celso!
ExcluirCada 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.
Amo seu blog... Como me ajuda!
ResponderExcluir