Como mesclar várias planilhas do Excel usando uma macro VBA

Mesclar dados de mais de uma planilha do Excel na mesma pasta de trabalho é um verdadeiro incômodo... até você usar macros VBA.

Excel VBA é parte integrante da automação do Excel, e o uso e os benefícios do VBA não podem ser prejudicados. Se você está em uma batalha difícil tentando consolidar várias planilhas e pastas de trabalho no Excel, estamos aqui para ajudar.

As macros mencionadas neste guia ajudarão você a realizar a tarefa aparentemente intransponível em questão de segundos (ou minutos, se os ativos de dados forem grandes).

Seguindo este tutorial, você criará sua própria macro VBA no Excel e mesclará várias planilhas com eficiência em uma única planilha.

Mesclando várias planilhas do Excel na mesma pasta de trabalho

Para esta tarefa, os dados são armazenados nas seguintes planilhas:

Folha1
Planilha2
Folha3

Os nomes das folhas listados acima são apenas para fins ilustrativos. Esta macro VBA é genérica e não depende dos nomes das planilhas; você pode personalizar o código para usá-lo com qualquer nome de planilha.

Pré-requisitos para executar o código

Existem alguns pré-requisitos para executar o código VBA listados abaixo.

Você precisa armazenar o código da macro em um novo arquivo Excel. Salve esta pasta de trabalho com uma extensão .xlsm . Você pode salvar a pasta de trabalho de macro VBA com qualquer nome.

Abra um novo arquivo Excel; pressione Alt + F11 no teclado para abrir o editor Excel VBA. Assim que o editor abrir, adicione um novo módulo de código clicando na guia Inserir na parte superior. Selecione Módulo para inserir um novo módulo; é aqui que você inserirá o código de macro VBA fornecido a seguir.

As fichas de dados a serem consolidadas devem estar em outra pasta de trabalho separada. O nome da pasta de trabalho e das planilhas pode ser o que você escolher.

Assim que você executar o código VBA, a macro VBA percorrerá cada planilha disponível na pasta de trabalho principal (pasta de trabalho de dados) e colará o conteúdo em uma planilha recém-adicionada na mesma pasta de trabalho.

Os dados consolidados estarão disponíveis na planilha denominada Consolidado.

Executando o código VBA

É hora de executar o código de macro recém-salvo. Copie e cole este código no módulo do editor VBA:

Sub consolidate_shts()
'declare the various variables used within the code and the vba data types
Dim sht As Worksheet, sht1 As Worksheet, lastrow As Integer, lastrow1 As Integer
'disable screen flickering and alert pop-ups during the execution
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
'store the name of the primary workbook in the a macro variable. Replace Test.xlsx with the name of your primary workbook
Set wbk1 = Workbooks("Test.xlsx")
'activate the workbook before performing the function(s) on it
wbk1.Activate
'run a vba for loop to check if a sheet Consolidated already exists. If it exists, the for loop will delete it.
For Each sht In wbk1.Sheets
If sht.Name = "Consolidated" Then sht.Delete
Next sht
'Add a new sheet to store the newly consolidated data
Worksheets.Add.Name = "Consolidated"
'Add some headers to each individual column within the consolidated sheet
With Sheets("Consolidated")
.Range("a1").Value = "OrderDate"
.Range("b1").Value = "Region"
.Range("c1").Value = "Rep"
.Range("d1").Value = "Item"
.Range("e1").Value = "Units"
.Range("f1").Value = "UnitCost"
.Range("g1").Value = "Total"

End With
'The newly created sheet consolidated will hold the consolidated data from each individual sheet in the primary workbook

For i = 1 To wbk1.Worksheets.Count
If Sheets(i).Name <> "Consolidated" Then
'Capture the last populated row from the data sheets in the workbook
lastrow = Sheets(i).Range("a1").End(xlDown).Row
'Capture the last populated row in the Consolidated sheet
lastrow1 = wbk1.Sheets("Consolidated").Range("a1048576").End(xlUp).Row + 1

'Copy data from source sheet and paste it in the consolidated sheet
Sheets(i).Range("a2:g" & lastrow).Copy Destination:=Sheets("Consolidated").Range("a" & lastrow1)
End If
Next i
'Enable Excel VBA functions for future use
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

O código VBA explicado

Primeiro, declare todas as variáveis que você está usando no código e atribua a elas os tipos de dados VBA corretos para fazer o código funcionar perfeitamente.

Depois de declarar as variáveis, algumas tarefas básicas serão necessárias. Isso é feito desativando a oscilação da tela e suprimindo alertas pop-up. Por exemplo, quando você exclui uma planilha existente usando o código VBA, um prompt no Excel solicita confirmação antes de excluir a planilha. Prompts como esse são suprimidos para aumentar a velocidade de execução.

Na próxima etapa, você precisa definir o nome da pasta de trabalho, que contém todos os seus dados. Substitua Test.xlsx pelo nome e extensão do nome da sua pasta de trabalho. Certifique-se de colocar o nome entre aspas.

Ative a pasta de trabalho principal e exclua todas as planilhas existentes com o nome Consolidado para eliminar quaisquer dados armazenados anteriormente. O código VBA alterna entre cada planilha e, assim que encontrar o nome da planilha Consolidado, ele a excluirá. Isso é feito usando a instrução VBA IF, que verifica as condições lógicas e exclui a planilha assim que a condição for atendida.

Uma nova planilha é adicionada à pasta de trabalho principal para armazenar os dados consolidados. Posteriormente, cabeçalhos padronizados e pré-formatados são adicionados a esta planilha. Você pode alterar os valores dos títulos (cabeçalhos das colunas) atualizando as informações próximas às referências das células entre aspas.

Por exemplo: .Range(“a1”)=“OrderDate” pode ser substituído por .Range(“a1”)=“OrderNumber”

Em seguida, um loop FOR do VBA alterna entre cada planilha, copia o conteúdo da planilha e cola o conteúdo na planilha Consolidada antes de passar para a próxima planilha da pasta de trabalho. Este processo se repete até que todas as folhas sejam copiadas.

Durante este processo, todas as linhas são calculadas automaticamente e coladas na planilha Consolidada. A última linha preenchida é calculada automaticamente antes de os dados serem colados. A macro é dinâmica e pode se ajustar a diversas linhas de dados em cada planilha.

Depois que os dados de todas as planilhas são colados na planilha de consolidação principal, a macro passa para a parte final do código. As funções VBA inicialmente desabilitadas são habilitadas novamente para uso futuro.

Consolidando várias planilhas usando macro VBA do Excel

Excel VBA é uma linguagem de programação supérflua, que funciona bem com todos os componentes do Excel. Cada trecho de código é essencial e é importante lembrar que a execução depende de um sistema de execução linha por linha, portanto você não deve alterar a ordem das linhas do código.