Split file from EXCEL

3

I have a base in an excel with 1 million emails in it, and I need to split 4 files of 250 thousand emails each, does anyone know how I can do this with some excel function? Thank you

    
asked by anonymous 12.06.2015 / 15:27

2 answers

3

If you already know the total number of cells, just do it manually, go straight to a certain line (250,000) by typing the number on the top left and pressing enter.

Oruseadvancedfilters.

This link has an example using advanced filter

    
12.06.2015 / 19:12
1

I think that a solution with VBA would be much more practical than a manual and tiring, so I did it here, if you do not have experience with macro , I left the code quite explained:

The idea is to get coluna A and divide it according to the divisor variable. The worksheets are saved in the default folder Meus Documentos . The only concern is that I quickly used the simplest approach method that always leverages: AsymArith , if you want another method, just look at that article in the link.

Sub Dividir_em_arquivos()
Dim ultimalinha As Integer, divisor As Integer
Dim resultado As Double
Dim nomeaba As String, nomearquivo As String, nomeaba_loop As String, nomearquivo_loop As String

'pega o nome da sheet e do arquivo que voce esta executando a macro
nomearquivo = ActiveWorkbook.Name
nomeaba = ActiveSheet.Name

'seleciona a ultima linha da COLUNA "A"
ultimalinha = Range("A" & Rows.Count).End(xlUp).Row

'------------- > coloque aqui divisor
divisor = 4

'calculo (SOMENTE NUMEROS PARES, usando o SymArith: https://support.microsoft.com/en-us/kb/196652/pt-br)
resultado = ultimalinha / divisor
resultado_apro = Int(resultado * 1 + 0.5) / 1


'loop para criar as planilhas e selecionar as colunas para copia
For i = 0 To divisor - 1
    resultado_inicio = (resultado_apro * i) + 1
    resultado_fim = resultado_apro * (i + 1)


    'set nome na planilha do loop
    nomeaba_loop = "numero" & i

    'cria planilha
    Set NewBook = Workbooks.Add
    With NewBook
        .Title = nomeaba_loop
        .SaveAs Filename:=nomeaba_loop
    End With

    'adiciona aba para colar as informações
    nomearquivo_loop = ActiveWorkbook.Name
    Sheets.Add.Name = nomeaba_loop

    'seleciona a planilha fonte
    Windows(nomearquivo).Activate
    Sheets(nomeaba).Select

    'Copia as informações da planilha fonte
    Range("A" & resultado_inicio & ":A" & resultado_fim).Copy

    'seleciona a planilha do loop
    Windows(nomearquivo_loop).Activate
    Sheets(nomeaba_loop).Select

    'cola as informações
    Range("A1:A" & resultado_apro).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    ActiveWorkbook.Save

    'seleciona novamente a planilha fonte para começar o loop
    Windows(nomearquivo).Activate
    Sheets(nomeaba).Select

Next i

End Sub
    
15.06.2015 / 22:05