Splitting a large Worksheet into multiple work books after n rows while keeping the header row

[Zaher503]

I have a large dataset of only 3 columns. I want this set to be split every x rows into a separate excel file, or workbook. I also want the header row to remain the same in all files.
If you want a sample of what I am looking for I can provide some screenshots.

I have looked everywhere without a solution so any help would be great.

 

[Marc L]

​

Many samples whatever the Excel forum; for more help better an attachment rather than screenshots …​

 

[ravi2628]

Hi Zaher,

I think this link can help you and I also copied macro for your reference.

Importing big text/csv file into excel using vba

I get the data in csv file and I need to import the data into excel. I use the below vba code to complete my task (which I also got from some site after modified accordingly): Sub ImportTextFile()...

stackoverflow.com

Sub textStreamToExcel()

'Add Scripting references in Tools before you write this code:
'Microsoft Scripting Control 1.0 and Microsoft Scripting Runtime

Dim numOfLines As Long
numOfLines = 10 '################### change this number to suit your needs

'Enter the source file name
Dim vFileName
vFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")

If vFileName = False Then
Exit Sub
End If

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim ts As TextStream
Dim line As String
Dim counter As Long

Set ts = fspenTextFile(vFileName, ForReading)

Dim wkb As Workbook
Set wkb = Workbooks.Add
wkb.Activate
'Save your file, enter your file name if you wish
Dim vSavedFile
vSavedFile = wkb.Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xls), *.xls")


If vSavedFile = False Then
Exit Sub
End If

wkb.SaveAs vSavedFile

Dim cwks As Integer
cwks = wkb.Sheets.Count

Dim iwks As Integer
iwks = 1
Dim wkbS As Excel.Worksheet

Application.ScreenUpdating = False
Looping:
counter = 1
If iwks <= cwks Then
Set wkbS = wkb.Worksheets(iwks)
wkbS.Activate
Range("A1").Activate

While counter <= numOfLines

If ts.AtEndOfStream <> True Then

line = ts.ReadLine
If ActiveCell.Value = "" Then
ActiveCell.Value = CStr(line)
End If
ActiveCell.Offset(1, 0).Activate
counter = counter + 1
Else
ts.Close
GoTo Ending
End If
Wend
Else
Set wkbS = wkb.Worksheets.Add(After:=Sheets(Sheets.Count))
wkbS.Activate
Range("A1").Activate

While counter <= numOfLines

If ts.AtEndOfStream <> True Then

'If the last line has been read it will give you an Input error
line = ts.ReadLine
If ActiveCell.Value = "" Then
ActiveCell.Value = CStr(line)
End If
ActiveCell.Offset(1, 0).Activate
counter = counter + 1
Else
ts.Close
GoTo Ending
End If
Wend
End If

iwks = iwks + 1

If ts.AtEndOfStream <> True Then
GoTo Looping
Else
GoTo Ending
End If

Ending:
Application.ScreenUpdating = True
Set fso = Nothing
Set ts = Nothing
Set wkb = Nothing
Set wkbS = Nothing
MsgBox "Transfer has been completed"
Exit Sub

ErrorHandler:

MsgBox "The following error has occured:" & Chr(13) & Chr(13) & "Error No: " & Err.Number * Chr(13) & "Description: " & Chr(13) & Err.Description

End Sub

 

[Marc L]

​

What a cumbersome code and does not match the need ‼​

 

[Zaher503]

​

Many samples whatever the Excel forum; for more help better an attachment rather than screenshots …​

Sample.csv
	A	B	C
1	Last Name	First Name	Email Address
2	Last Name 1	First Name 1	example1@gmail.com
3	Last Name 2	First name 2	example2@gmail.com
4	Last Name 3	First Name 3	example3@gmail.com
5	Last Name 4	First Name 4	example1@gmail.com
6	Last Name 5	First Name 5	example2@gmail.com
7	Last Name 6	First Name 6	example3@gmail.com
8	Last Name 7	First Name 7	example1@gmail.com
9	Last Name 8	First Name 8	example2@gmail.com
10	Last Name 9	First Name 9	example3@gmail.com
11	Last Name 10	First Name 10	example1@gmail.com
12	Last Name 11	First Name 11	example2@gmail.com
13	Last Name 12	First Name 12	example3@gmail.com
14	Last Name 13	First Name 13	example1@gmail.com
15	Last Name 14	First Name 14	example2@gmail.com
16	Last Name 15	First Name 15	example3@gmail.com
17	Last Name 16	First Name 16	example1@gmail.com
18	Last Name 17	First Name 17	example2@gmail.com
19	Last Name 18	First Name 18	example3@gmail.com
20	Last Name 19	First Name 19	example1@gmail.com
21	Last Name 20	First Name 20	example2@gmail.com
22	Last Name 21	First Name 21	example3@gmail.com
23	Last Name 22	First Name 22	example1@gmail.com
24	Last Name 23	First Name 23	example2@gmail.com
25	Last Name 24	First Name 24	example3@gmail.com
26	Last Name 25	First Name 25	example1@gmail.com
27	Last Name 26	First Name 26	example2@gmail.com
28	Last Name 27	First Name 27	example3@gmail.com
29	Last Name 28	First Name 28	example1@gmail.com
30	Last Name 29	First Name 29	example2@gmail.com
31	Last Name 30	First Name 30	example3@gmail.com
32	Last Name 31	First Name 31	example1@gmail.com
33	Last Name 32	First Name 32	example2@gmail.com
34	Last Name 33	First Name 33	example3@gmail.com
35	Last Name 34	First Name 34	example1@gmail.com
36	Last Name 35	First Name 35	example2@gmail.com
Sample

I am not very familiar with this tool so I did not know I can do this. But here is a sample of my file. It goes down to 5000 rows and I want to keep the Header row in all new files.

 

[Marc L]

I want this set to be split every x rows into a separate excel file, or workbook.

So what means 'x' ? And which kind of files to create ? And Where ? And … As Accuracy leads to a quicker and more targeted solution …​

 

[Marc L]

​

As your source file is not an Excel workbook but just a text file so for a faster procedure the better is you link it on a files host website like Dropbox …​

​

As it's a text file did you test the post #3 code ?​

 

[Zaher503]

​

As your source file is not an Excel workbook but just a text file so for a faster procedure the better is you link it on a files host website like Dropbox …​

​

As it's a text file did you test the post #3 code ?​

I tried post #3 but it did not work. I got a compiler error: User-defined type not defined
I have uploaded a sample of the file I am working with if you want to take a look.
Dropbox

 

[Marc L]

Ok for the sample text file but any answer to my post #6 ?!​

 

[Zaher503]

So what means 'x' ? And which kind of files to create ? And Where ? And … As Accuracy leads to a quicker and more targeted solution …​

Lol you are right. My post was very vague, I am sorry.
1- Let's say every 100 rows. But is there a way to customize this in case I want more rows in the future?
2- Creating an xlsx file would be great.
3- In a folder called output in the same directory as the origin file.
4- file name for output can be sequential numbers starting at 1.xlsx

If there is any more information you need just let me know!