VBA to split 1 file into multiple files

[Maxy]

• Hi guys,
• 1st time posting in this forum and really need help to solve my problem .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

 

[Luke M]

Do any of the links here look helpful? There is quite a lot of work if building from scratch for this task.
http://www.rondebruin.nl/win/section3.htm

 

[Smallman]

Hi Maxy

Give the following a try. What do you want to call each file? Where do you want to save the files? The code is a bit of a pain as the top and bottom of the ranges both need to move at the same time. What do you want to do about a heading in the saved fiels? Easily added.

I will attach a workbook to show workings.

Option Explicit
Sub Testo()
Dim i As Integer
Dim n As Integer
Dim j As Integer
Dim k As Integer
Dim res As Integer
 
j = 2
res = InputBox("Enter a figure between 1 & 10")
n = Round(Range("A" & Rows.Count).End(xlUp).Row / res, 0)
k = n + 1
 
    For i = 1 To res
        Workbooks.Add
        Sheet1.Range("A" & j & ":F" & k).Copy [a2]
        ActiveWorkbook.Close True, j & "to " & k & ".xls"
        j = j + n
        k = k + n
    Next i
End Sub

Take care

Smallman

 

[Maxy]

Hi Guys,

Thanks so much for the reply. I have finish the code with some help from other people too.
@Luke M , thanks for the link man, it does help and i'm sure i will still need it in the future
@Smallman, i also used some part of your code , thanks for the help