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
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
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
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