Às vezes, queremos mesclar várias planilhas em uma planilha para que possamos analisar facilmente os dados e transformá-los em algumas informações úteis. Este artigo irá lhe ensinar como mesclar várias planilhas em uma planilha usando o VBA.
Exemplo:
Aqui, busquei alguns dados do servidor que retornam dados em planilhas diferentes. Eu adicionei mais uma planilha e a chamei de “Mestre”. Outros nomes de planilha não importam.
Agora execute esta macro.
Sub Merge_Sheets () Dim startRow, startCol, lastRow, lastCol As Long Dim headers As Range 'Definir planilha mestre para consolidação Set mtr = Worksheets ("Master") Set wb = ThisWorkbook' Get Headers Set headers = Application.InputBox ("Selecione o Headers ", Type: = 8) 'Copiar Headers into master headers.Copy mtr.Range (" A1 ") startRow = headers.Row + 1 startCol = headers.Column Debug.Print startRow, startCol' percorre todas as planilhas Para Cada ws Em wb.Worksheets 'exceto a folha mestre de loop If ws.Name "Master" Then ws.Activate lastRow = Cells (Rows.Count, startCol) .End (xlUp) .Row lastCol = Cells (startRow, Columns.Count). End (xlToLeft) .Column 'obtém os dados de cada planilha e os copia para o intervalo da planilha mestre (Cells (startRow, startCol), Cells (lastRow, lastCol)). Copiar _ mtr.Range ("A" & mtr.Cells (Rows .Count, 1) .End (xlUp) .Row + 1) End If Next ws Worksheets ("Master"). Ative End Sub
Como mesclar planilhas usando esta macro VBA?
- Insira uma nova planilha com o nome “Mestre” na pasta de trabalho. Renomeie-o mais tarde, se desejar.
- Insira um módulo no editor VBA e copie o código VBA acima.
- Execute a macro.
- Você será solicitado a selecionar títulos. Selecione o título e clique em OK.
E está feito. Todas as folhas são mescladas no mestre.
Como funciona?
Presumo que você conheça os fundamentos da criação de objetos e variáveis em VBA. na primeira parte, criamos objetos e variáveis de que precisaremos em nossas operações.
Bem, a maioria das coisas que expliquei usando comentários no código vba. Vejamos a parte principal deste código vba.
For Each ws In wb.Worksheets 'exceto a planilha master do loop If ws.Name "Master" Then ws.Activate lastRow = Cells (Rows.Count, startCol) .End (xlUp) .Row lastCol = Cells (startRow, Columns. Count) .End (xlToLeft) .Column 'obtém dados de cada planilha e os copia para o intervalo da planilha mestre (Células (linha inicial, linha inicial), Células (linha final, linha final)). Copiar _ mtr.Range ("A" & mtr. Células (Rows.Count, 1) .End (xlUp) .Row + 1) End If Next ws
Em artigos anteriores, aprendemos como percorrer planilhas e como obter a última linha e coluna usando vba.
Aqui, estamos percorrendo cada planilha da pasta de trabalho principal usando o loop for.
Para cada ws em wb.Worksheets
Em seguida, excluímos a planilha “master” do loop, uma vez que estaremos consolidando nossos dados nessa planilha.
Em seguida, obtemos a última linha e o número da última coluna.
Agora a próxima linha é muito importante. Fizemos várias operações em uma linha.
Intervalo (Células (startRow, startCol), Células (lastRow, lastCol)). Copiar _
mtr.Range ("A" & mtr.Cells (Rows.Count, 1) .End (xlUp) .Row + 1)
Primeiro, formamos um intervalo usando startRow, startCol e lastRow e lastCol.
Range (Cells (startRow, startCol), Cells (lastRow, lastCol)) Nós o copiamos usando o método de cópia de range. Intervalo (Células (startRow, startCol), Cells (lastRow, lastCol)). Copiar Colamos diretamente na primeira célula em branco após a última célula não em branco na coluna A da planilha mestre (mtr.Cells (Rows.Count, 1) .End (xlUp) .Row + 1). Range (Cells (startRow, startCol), Cells (lastRow, lastCol)). Copiar _ mtr.Range ("A" & mtr.Cells (Rows.Count, 1) .End (xlUp) .Row + 1)
Este loop é executado para todas as planilhas e copia os dados de cada planilha na planilha mestre.
Por fim, no final da macro, ativamos a planilha mestre para ver o resultado.
Então, sim pessoal, é assim que você pode mesclar todas as planilhas em uma pasta de trabalho. Deixe-me saber se você tiver qualquer dúvida sobre este código VBA ou qualquer tópico do Excel na seção de comentários abaixo.
⇬ Fazer download do arquivo:
Artigos relacionados:
Como fazer loop em folhas
como obter a última linha e coluna usando vba
Exclua planilhas sem prompts de confirmação usando VBA no Microsoft Excel
Adicionar e salvar uma nova pasta de trabalho usando VBA no Microsoft Excel 2016
Exibir uma mensagem na barra de status do Excel VBA
Desativar mensagens de aviso usando VBA no Microsoft Excel 2016
Artigos populares:
A função VLOOKUP no Excel
CONT.SE no Excel 2016
Como usar a função SUMIF no Excel