Combine tabelas em várias planilhas para uma planilha

Question

Eu tenho um documento do excel com mais de 30 folhas. Cada um tem o mesmo layout: uma tabela simples com as mesmas colunas, cabeçalho / total de linhas, mas diferentes números de linhas (nenhuma tem mais de 500 linhas).

Eu quero combiná-los em uma tabela mestre. Apenas uma operação de tempo.

Manualmente, eu poderia copiar 30 tabelas uma após a outra em uma nova planilha. Quando terminar, eu faria uma ordenação e excluiria qualquer linha em branco / cabeçalho / total. Eu não me importo com a formatação. Eu acho que levaria cerca de 15-20 minutos, mas sempre há o risco de eu cometer um erro e perder uma folha ou algo assim.

O VBA parece que levaria muito tempo. Agora estou quase considerando importar a pasta de trabalho para o SQL Server, UNION-los e copiar e colar o resultado de volta ao excel.

Você tem algum truque bom?

    

Answer 1

Se isso é algo que você precisará fazer com frequência, recomendo escrever uma rotina rápida de VBA. Se é uma coisa de uma só vez, então eu faria isso manualmente. Para garantir que você não pule acidentalmente uma tabela, use o recurso de copiar e colar em vez de copiar e colar.

Aqui está como eu faria isso. Funciona rapidamente com os atalhos de teclado.

• Abra a pasta de trabalho original no Excel como somente leitura para evitar perda acidental de dados.
• Abra uma nova pasta de trabalho para manter a nova tabela mestre.
• Feche todas as outras pastas de trabalho para facilitar a alternância entre as duas com as quais você está trabalhando.
• Selecione a primeira planilha na pasta de trabalho original que você deseja copiar.

Agora você pode começar a usar atalhos de teclado.

1. Ctrl + Home para ir para o canto superior esquerdo da planilha. (Isso irá pular para A1, a menos que haja linhas ou colunas congeladas.)
2. Shift + Ctrl + End para selecionar tudo para a esquerda e para baixo da célula selecionada atual.
3. Ctrl + X para cortar os dados.
4. Ctrl + Tab para mudar para a nova pasta de trabalho.
5. Verifique se você está no local correto e pressione Enter para colar os dados.
6. Ctrl + End para ir para a última célula colada.
7. Down Arrow para ir para a próxima linha não utilizada.
8. Ctrl + Left Arrow para voltar para a coluna A
9. Ctrl + Tab para voltar ao livro original.
10. Ctrl + Page Down para selecionar a próxima planilha.
11. Volte para o passo 1 e repita até terminar.

Quando você terminar, poderá Ctrl + Page Up voltar a todas as planilhas originais e verificar se todos os dados foram removidos.

Supondo que sua linha de título não esteja congelada, copiará a linha do título de todas as 30 tabelas. Nesse caso, ordene a tabela principal para agrupar todas as linhas de título duplicadas e exclua os extras ou use Dados - > Remover duplicados.

Se você ignorar acidentalmente uma etapa ou colar dados existentes, pressione Ctrl + Z para desfazer.

Acabei de fazer um teste seguindo as etapas acima. A pasta de trabalho que usei tinha 10 planilhas, cada uma com 120 linhas de dados. Demorei 1 ½ minutos para criar a tabela mestre. Se você não estiver familiarizado com os atalhos de teclado, isso pode levar o dobro do tempo. Para 30 planilhas, eu acho que pode levar 10 minutos.

    

Answer 2

Eu adiciono o mesmo problema, encontrei uma macro incrível aqui: link

Sub Combine()
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A1").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
End Sub

Copie e cole a macro, execute-a e pronto!