VBA to split 1 file into multiple files

Hi guys,

I need help for making a code to split my master file into multiple files.

My data :

- My data is from A2 to F601
- A1:F1 is the header

My requirements :

- I need a pop up message " how many data per batch ", and when i type like for example 200, there will be 3 excel files in my Import folder ( located in the same folder with my master file )
- The 3 files will have a name and the data { 1st file = File1to200 (containing data of master file from row A2:F201) , 2nd file = File201to400 (data from row A202:F401, 3rd file = File401to600 (data from row A402:F601 } , in short the data will be 200 rows for each file

It will be really helpful if some1 can help me in this problem, i will be glad to provide sample data or further explanation if necessary.
Any kind of help will be appreciated ( The code, an explanation to make the code, or a website that can help me to make the code )

Thanks in advance guys

Maybe:

Sub Maxyz()
Dim ws As Worksheet
Dim x As Long
Dim y As Long
Dim z As Long
Dim w As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveSheet
x = InputBox("How many data per batch?")
y = 3
z = 1
w = x
Do Until y = 0
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="D:\Common\data\IBMmain\" & "File" & z & "to" & x & ".xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Sheets("Sheet1").Range("A1").EntireRow.Value = ws.Range("A1").EntireRow.Value
ws.Range("A" & z + 1 & ":" & "F" & x + 1).Copy Workbooks("File" & z & "to" & x & ".xls").Sheets("Sheet1").Range("A" & Rows.count).End(3)(2)
z = z + w
x = x + w
y = y - 1
Loop
Application.ScreenUpdating = True
Application.ScreenUpdating = True
End Sub

You will need to change the file path.

Hi John,

Thank you so much for helping me again , really appreaciate it man. I tried the code and it indeed split the data into 3 file but there are some several problem that i wish you can help me with again due to my lack of knowledge about coding for now.

1. The 3 file that comes out are in excel 2003, and i actually need excel 2010, i tried to change this part of the code

Copy Workbooks("File" & z & "to" & x & ".xls")

into .xlsx but it didnt work.
2. I need the 3 file to be saved and closed at the same time ( for now the 3 files are all open when i run the code )
So it will be like when i click the button, the file is just showed up in the folder
3. and the last thing is when i enter 100 into the inputbox, it indeed split the data 100 each file but there are just 3 files in total, it suppose to become 6 files.( Is this possible?)

Thank you so much for the help.

Hi John,

Sorry, nevermind about the 1st problem about excel 2010 format, i got that part already, for the other problem can you help me?

Will be really glad if someone else can also share some knowledge in this matter . Thanks

What determines how files are to be created? In Post 1, you say 3. In Post 3, you say 6.

Maybe:

Sub Maxyz()
Dim ws As Worksheet
Dim x As Long
Dim y As Long
Dim z As Long
Dim w As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveSheet
x = InputBox("How many data per batch?")
y = 6
z = 1
w = x
Do Until y = 0
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="D:\Common\data\IBMmain\" & "File" & z & "to" & x & ".xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Sheets("Sheet1").Range("A1").EntireRow.Value = ws.Range("A1").EntireRow.Value
ws.Range("A" & z + 1 & ":" & "F" & x + 1).Copy Workbooks("File" & z & "to" & x & ".xls").Sheets("Sheet1").Range("A" & Rows.count).End(3)(2)
z = z + w
x = x + w
y = y - 1
ActiveWorkbook.Close True
Loop
Application.ScreenUpdating = True
Application.ScreenUpdating = True
End Sub

Hi John,

Im sorry if i didn't explain it clearly, what i mean before is my data is 600 rows ( this is just for example because the data always varies everytime ) , so when the pop up show up and i enter for example 100 ( this means how many rows per file ). it will create 6 files for me because 1 file contain 100 rows.

Example :
1. I type 250 , it become 3 files ( the 3rd file only contain 100 data because the 1st 2 file contain 250 data each )
2. I type 50, it become 12 files

Please ask me if there is still anything unclear. Thanks

I'm not certain I understand but try

Sub Maxyz()
Dim ws As Worksheet
Dim x As Long
Dim y As Long
Dim z As Long
Dim w As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveSheet
x = InputBox("How many data per batch?")
y = ws.usedrange.rows.count/x
z = 1
w = x
Do Until y = 0
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="D:\Common\data\IBMmain\" & "File" & z & "to" & x & ".xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Sheets("Sheet1").Range("A1").EntireRow.Value = ws.Range("A1").EntireRow.Value
ws.Range("A" & z + 1 & ":" & "F" & x + 1).Copy Workbooks("File" & z & "to" & x & ".xls").Sheets("Sheet1").Range("A" & Rows.count).End(3)(2)
z = z + w
x = x + w
y = y - 1
ActiveWorkbook.Close True
Loop
Application.ScreenUpdating = True
Application.ScreenUpdating = True
End Sub

Hi John,

Sorry for the late reply and thank you so much for all your help. The code works perfect, really appreciate it. Thanks man

You're welcome. Glad to help out and thanks for the feedback.

This is a great macro. Is there any way to modify it so that the first row is included in every new workbook?

Thanks,
Marion

Hi Marion:

Welcome to the Forum. For furture reference please comply with Forum Rule No. 2. BTW the macro already copies the first row of the activesheet to Sheet1 of each newly created workbook.