Como consolidar várias pastas de trabalho do Excel usando VBA

Se você deseja consolidar várias pastas de trabalho do Excel com o mínimo de esforço, você veio ao lugar certo.

Ao trabalhar com fontes de dados variadas, muitas vezes você pode ter dificuldade para compilar várias pastas de trabalho e planilhas antes de chegar a um dado final. Imagine uma situação em que você tem algumas centenas de pastas de trabalho para combinar antes mesmo de começar o dia.

Ninguém quer passar horas intermináveis trabalhando em fontes diferentes, abrindo cada pasta de trabalho, copiando e colando os dados de várias planilhas, antes de finalmente fazer uma pasta de trabalho consolidada. E se uma macro VBA puder fazer isso por você?

Com este guia, você pode criar seu próprio código de macro VBA do Excel para consolidar várias pastas de trabalho, tudo em questão de minutos (se houver muitos arquivos de dados).

Pré-requisitos para criar seu próprio código macro VBA

Você precisa de uma pasta de trabalho para armazenar o código VBA, enquanto o restante das pastas de trabalho de dados de origem são separados. Além disso, crie uma pasta de trabalho Consolidada para armazenar os dados consolidados de todas as suas pastas de trabalho.

Crie uma pasta Consolidação no local de sua preferência para armazenar todas as suas pastas de trabalho de origem. Quando a macro é executada, ela alterna entre cada pasta de trabalho armazenada nesta pasta, copia o conteúdo de várias planilhas e o coloca na pasta de trabalho Consolidada.

Criando seu próprio código Excel VBA

Depois que os pré-requisitos estiverem resolvidos, é hora de se aprofundar no código e começar a explorar o básico para adaptá-lo aos seus requisitos.

Pressione a tecla Alt+F11 no Excel para abrir o editor de código de macro VBA. Cole o código escrito abaixo e salve o arquivo como uma pasta de trabalho habilitada para macro (extensão .xlsm ).

 Sub openfiles()&#39;declare the variables used within the VBA codeDim MyFolder As String, MyFile As String, wbmain As Workbook, lastrow As Long&#39;disable these functions to enhance code processingWith Application.DisplayAlerts = False.ScreenUpdating = FalseEnd With&#39;change the path of the folder where your files are going to be savedMyFolder = InputBox("Enter path of the Consolidation folder") & "\"&#39;define the reference of the folder in a macro variableMyFile = Dir(MyFolder)&#39;open a loop to cycle through each individual workbook stored in the folderDo While Len(MyFile) > 0&#39;activate the Consolidation workbookWindows("Consolidation").Activate&#39;calculate the last populated rowRange("a1048576").SelectSelection.End(xlUp).SelectActiveCell.Offset(1, 0).Select&#39;open the first workbook within the Consolidation folderWorkbooks.Open Filename:=MyFolder & MyFileWindows(MyFile).Activate&#39;toggle through each sheet within the workbooks to copy the dataDim ws As WorksheetFor Each ws In Sheets ws.Activate ws.AutoFilterMode = False &#39;ignore the header and copy the data from row 2 If Cells(2, 1) = "" Then GoTo 1 GoTo 101: Next10: Range("a2:az20000").CopyWindows("Consolidation").Activate&#39;paste the copied contentsActiveSheet.PasteWindows(MyFile).Activate&#39;close the open workbook once the data is pastedActiveWorkbook.Close&#39;empty the cache to store the value of the next workbookMyFile = Dir()&#39;open the next file in the folderLoop&#39;enable the disabled functions for future useWith Application.DisplayAlerts = True.ScreenUpdating = TrueEnd WithEnd Sub

O código VBA explicado

A primeira parte do código é definir uma sub-rotina, que contém todo o seu código VBA. Defina a sub-rotina com sub, seguido do nome do código. O subnome pode ser qualquer coisa; idealmente, você deve manter um nome relevante para o código que está prestes a escrever.

Excel VBA entende variáveis criadas pelo usuário e seus tipos de dados correspondentes declarados com dim (dimensão).

Para aumentar a velocidade de processamento do seu código, você pode desativar a atualização da tela e suprimir todos os alertas, pois isso retarda a execução do código.

O usuário será solicitado a fornecer o caminho da pasta onde os arquivos de dados estão armazenados. Um loop é criado para abrir cada pasta de trabalho armazenada na pasta, copiar os dados de cada planilha e anexá-los à pasta de trabalho Consolidação.

A pasta de trabalho Consolidação é ativada para que o Excel VBA possa calcular a última linha preenchida. A última célula da planilha é selecionada e a última linha é calculada na pasta de trabalho usando a função de deslocamento. Isso é muito útil quando a macro começa a anexar dados dos arquivos de origem.

À medida que o loop abre o primeiro arquivo de origem, os filtros são removidos de cada planilha (se existirem) e os dados que variam de A2 a AZ20000 serão copiados e colados na pasta de trabalho Consolidação.

O processo é repetido até que todas as planilhas da pasta de trabalho sejam anexadas à pasta de trabalho mestre.

Finalmente, o arquivo de origem é fechado assim que todos os dados são colados. A próxima pasta de trabalho é aberta para que a macro VBA possa repetir as mesmas etapas para o próximo conjunto de arquivos.

O loop é codificado para ser executado até que todos os arquivos sejam atualizados automaticamente na pasta de trabalho mestre.

Personalizações baseadas no usuário

Às vezes, você não quer se preocupar com prompts embutidos, especialmente se você for o usuário final. Se preferir codificar o caminho da pasta Consolidação no código, você pode alterar esta parte do código:

 MyFolder = InputBox("Enter path of the Consolidation folder") & "\"

Para :

 MyFolder = &ldquo;Folder path&rdquo; & "\"

Além disso, você também pode alterar as referências das colunas, pois a etapa não está incluída neste código. Basta substituir a referência da coluna final pelo último valor da coluna preenchida (AZ, neste caso). Você precisa lembrar que a última linha preenchida é calculada por meio do código de macro, portanto, você precisa alterar apenas a referência da coluna.

Para aproveitar ao máximo esta macro, você pode usá-la apenas para consolidar pastas de trabalho no mesmo formato. Se as estruturas forem diferentes, você não poderá usar esta macro VBA.

Consolidando várias pastas de trabalho usando macro VBA do Excel

Criar e modificar um código VBA do Excel é relativamente fácil, especialmente se você compreender algumas das nuances do código. O VBA percorre sistematicamente cada linha de código e o executa linha por linha.