Assistir ao vídeo - Como dividir cada planilha do Excel em arquivos separados
Se você tiver uma pasta de trabalho do Excel com muitas planilhas, não haverá uma maneira fácil de dividir cada uma dessas planilhas em arquivos Excel separados e salvá-las separadamente.
Isso pode ser necessário quando você planeja planilhas para diferentes meses ou regiões ou produtos / clientes e deseja obter rapidamente uma pasta de trabalho separada para cada planilha (como um arquivo Excel ou PDFs).
Embora haja uma maneira manual de dividir as planilhas em pastas de trabalho separadas e salvá-las, é ineficiente e sujeito a erros.
Neste tutorial, darei a você um código VBA simples que você pode usar para rapidamente (em alguns segundos) dividir todas as planilhas em seus próprios arquivos separados e salvá-los em qualquer pasta especificada.
Divida cada planilha em um arquivo separado do Excel
Suponha que você tenha uma pasta de trabalho, conforme mostrado abaixo, onde você tem uma planilha para cada mês.
Para dividir essas planilhas em um arquivo Excel separado, você pode usar o código VBA abaixo:
'Código criado por Sumit Bansal de trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = Falso Application.DisplayAlerts = Falso para cada ws em ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs Nome do arquivo: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Existem algumas coisas que você precisa verificar antes de usar o código VBA acima:
- Crie uma pasta onde deseja obter todos os arquivos resultantes.
- Salve o arquivo Excel principal (que contém todas as planilhas que você deseja como arquivos separados) nesta pasta.
Depois de fazer isso, você pode colocar o código VBA acima no arquivo e executar o código.
O código acima é escrito de forma a pegar a localização da pasta usando o caminho do arquivo (no qual o código é executado). É por isso que é importante salvar o arquivo na pasta primeiro e, em seguida, usar este código.
Como funciona o código VBA - O código acima usa um loop For Next simples que percorre cada planilha, cria uma cópia da planilha em uma planilha do Excel e, em seguida, salva esta planilha do Excel na pasta especificada (que é a mesma que tem o arquivo principal com todos os folhas).
Abaixo estão as etapas para colocar este código VBA na pasta de trabalho do Excel (eles serão os mesmos para todos os outros métodos mostrados neste tutorial):
Onde colocar esse código?
Abaixo estão as etapas para colocar o código no Editor do Visual Basic onde ele pode ser executado:
- Clique na guia Desenvolvedor.
- No grupo Código, clique na opção Visual Basic. Isso abrirá o Editor VB. [Você também pode usar o atalho do teclado - ALT + F11]
- No Editor VB, clique com o botão direito em qualquer um dos objetos da pasta de trabalho em que você está trabalhando.
- Passe o cursor sobre a opção Inserir
- Clique em Módulo. Isso irá inserir um novo módulo
- Clique duas vezes no objeto Módulo. isso irá abrir a janela de código para o módulo
- Copie o código VBA fornecido acima e cole-o na janela de código do módulo.
- Selecione qualquer linha no código e clique no botão play verde na barra de ferramentas para executar o código da macro VBA.
As etapas acima dividiriam instantaneamente as planilhas em arquivos Excel separados e os salvariam. Leva apenas um segundo se você tiver menos número de planilhas. Caso você tenha muito, pode demorar um pouco.
O nome de cada arquivo salvo é igual ao nome da planilha que havia no arquivo principal.
Como você colocou um código VBA na pasta de trabalho do Excel, precisa salvá-lo com um formato .XLSM (que é o formato habilitado para macro). Isso garantirá que a macro seja salva e funcione na próxima vez que você abrir este arquivo.
Observe que usei as linhas Application.ScreenUpdating = False e Application.DisplayAlerts = False no código para que tudo aconteça no back-end e não veja as coisas acontecendo em sua tela. Uma vez que o código é executado, divide as planilhas e as salva, nós as transformamos de volta em TRUE.
Como prática recomendada, é recomendável criar uma cópia de backup do arquivo principal (que contém as planilhas que você deseja dividir). Isso garantirá que você não perderá seus dados caso algo dê errado ou se o Excel decidir ficar lento ou falhar.
Divida cada planilha e salve como PDFs separados
Caso queira dividir as planilhas e salvá-las como arquivos PDF em vez de arquivos do Excel, você pode usar o código abaixo:
'Código criado por Sumit Bansal de trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = Falso Application.DisplayAlerts = Falso para cada ws em ThisWorkbook.Sheets ws.Copy Application.ActiveSheet.ExportAsFixedFormat Tipo: = xlTypePDF, Nome do arquivo: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Coisas que você precisa ter certeza antes de usar este código:
- Crie uma pasta onde deseja obter todos os arquivos resultantes.
- Salve o arquivo principal do Excel (que contém todas as planilhas que você deseja como arquivos separados) nesta pasta.
O código acima divide cada folha do arquivo Excel e salva como PDF na mesma pasta onde você salvou o arquivo Excel principal.
Divida apenas as planilhas que contêm uma palavra / frase em arquivos separados do Excel
Caso você tenha muitas planilhas em uma pasta de trabalho e queira dividir apenas as planilhas que contenham um texto específico, você também pode fazer isso.
Por exemplo, suponha que você tenha um arquivo Excel no qual seus dados são de vários anos e cada planilha do arquivo tem o número do ano como prefixo. Algo conforme mostrado abaixo:
Agora, digamos que você queira dividir todas as planilhas de 21-2022 e salvá-las como arquivos Excel separados. Para fazer isso, você precisa verificar de alguma forma o nome de cada planilha e apenas aquelas planilhas que têm o número 2021-2022 devem ser divididas e salvas, e o resto deve ser deixado intocado.
Isso pode ser feito usando o seguinte código de macro VBA:
'Código criado por Sumit Bansal de trumpexcel.com Sub SplitEachWorksheet () Dim FPath As String Dim TexttoFind As String TexttoFind = "2020" FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = Falso Application.DisplayAlerts = Falso para cada ws neste livro. Sheets If InStr (1, ws.Name, TexttoFind, vbBinaryCompare) 0 Then ws.Copy Application.ActiveWorkbook.SaveAs Filename: = FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
No código acima, usei uma variável TexttoFind, que foi atribuída a “2020” no início.
O código VBA então usa o loop For Next no VBA para percorrer cada planilha e então verificar o nome de cada função INSTR da planilha. Esta função verifica se o nome da planilha contém a palavra 2021-2022 ou não. Em caso afirmativo, ele retornará um número de posição onde encontra esse texto (que é 2021-2022 neste caso).
E se não encontrar o texto que procuramos, retornará 0.
Isso é usado com a condição IF Then. Portanto, se o nome de uma planilha contiver a string de texto 2021-2022, ela será dividida e salva como um arquivo separado. E se não tiver essa string de texto, a condição IF não seria atendida e nada aconteceria.
