Hi,

How to extract 20 000 lines out of 200 000 lines automatically and then
transfer them to another xls file automatically. Hope I am clear enough

Thanks
--
DD

Hi DD,
You will need to give us a bit more info.
What is special about each line that you want to extract?
How many columns in each line?
Is the data contiguous? (are there any blank cells within the 200,000
lines?)Regards - Dave.

"deedee" wrote:

Hi,

How to extract 20 000 lines out of 200 000 lines automatically and then
transfer them to another xls file automatically. Hope I am clear enough

Thanks
--
DD

Hi,

I have about 200 000 lines with infos like names, addresses (8 columns) but
with some blank cells. I would like to extract 10 xls files into 20 000 lines
(no specific criterias except the number of lines which is 20 000.

If you could help it would be great. I have access too if it's more simple
with access

Thanks
--
DD


"Dave" wrote:

Hi DD,
You will need to give us a bit more info.
What is special about each line that you want to extract?
How many columns in each line?
Is the data contiguous? (are there any blank cells within the 200,000
lines?)Regards - Dave.

"deedee" wrote:

Hi,

How to extract 20 000 lines out of 200 000 lines automatically and then
transfer them to another xls file automatically. Hope I am clear enough

Thanks
--
DD

Hi DD
Paste the following into a module of your main workbook.

Sub DoWhatDDNeeds5()
A = 1
B = ThisWorkbook.Name
C = ActiveSheet.Name
D = "Split " & A & ".xls"
E = 2
F = 10
Do Until Workbooks(B).Sheets(C).Cells(E, 1) = ""
D = "Split " & A & ".xls"
Workbooks.Add.SaveAs Filename:=D
Workbooks(B).Activate
Workbooks(B).Sheets(C). _
Range(Cells(E, 1), Cells(F, 8)) _
.Copy Workbooks(D).Sheets(1).Range("A2")
A = A + 1
E = F + 1
F = F + 10
Loop
End Sub

Activate the sheet that has the data, then run the code.
It will generate about 10 new workbooks called Split 1, Split 2 etc, each
containg 20,000 rows of data, and leave them all open. You need to save them
to wherever you want. Your original workbook will be unaffected.
Note: If your computer complains about not having enough memory to have all
the workbooks open, let me know.
Also, if you need header rows in each workbook, we could do that too.

Regards - Dave.

Oops, sorry. I sent the the wrong code. I didn't have 200,000 lines of data,
so I made up a miniature version. The code for the real thing is:

Sub DoWhatDDNeeds5()
A = 1
B = ThisWorkbook.Name
C = ActiveSheet.Name
D = "Split " & A & ".xls"
E = 2
F = 20000
Do Until Workbooks(B).Sheets(C).Cells(E, 1) = ""
D = "Split " & A & ".xls"
Workbooks.Add.SaveAs Filename:=D
Workbooks(B).Activate
Workbooks(B).Sheets(C). _
Range(Cells(E, 1), Cells(F, 8)) _
..Copy Workbooks(D).Sheets(1).Range("A2")
A = A + 1
E = F + 1
F = F + 20000
Loop
End Sub