How to Split Ver Large Exel File Into Smaller Files of 1000 Rows in Size

Hi,

Welcome to the forum.

Press Alt+F11, then go to Insert, Module and paste in the following code into the module sheet:

I assume you want to header row on each sheet, make sure you have a header row in row 1.

Sub SplitUp()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim lastrow As Long, x As Long, p As Long, y As String, i As Long
Dim ws As Worksheet

Worksheets("sheet1").Activate

lastrow = Range("A1").End(xlDown).Row


x = InputBox("How many rows per sheet would you like?")
p = 0

Do
ActiveWorkbook.Names.Add Name:="y", RefersToR1C1:="=Sheet1!R1"

If p * x + 2 > lastrow Then Exit Do
Range(Cells(p * x + 2, 1), Cells(p * x + 2 + x - 1, 1)).EntireRow.Copy
Worksheets.Add

ActiveSheet.Name = p * x + 1 & "-" & p * x + 1 + x - 1

With ActiveSheet
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
 End With
Worksheets("sheet1").Activate
p = p + 1
Loop
For i = 1 To Worksheets.Count
If Worksheets(i).Name <> "Sheet1" Or Worksheets(i).Name <> "Sheet2" Then
 Worksheets("sheet1").Range("y").Copy
 Worksheets(i).Activate
Range("A1").PasteSpecial

End If
Next i

For Each ws In ActiveWorkbook.Worksheets
ws.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name
ActiveWorkbook.Close
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Run the code... alternatively you can open the workbook, paste in your data into sheet 1 and press the split up button.

Let me know if you have any issues with it or if this is what you were after.

Thanks.