# create new workbook after every 995 rows



## Hiten_123 (Aug 29, 2022)

I have an application which doesn't support to bulk upload the data of more than 999 rows so, everytime I have to create a new file manually and data count is more than 10,000 rows. 

Please help me to create a macro or formulae which can create a new workbook after every 995 rows and save as "file name 1/2/3...." in designated path


----------



## Colo (Aug 30, 2022)

Welcome to the board!
Here's a sample code for you. Place it in a standard module then give it a try.


```
Sub Sample()
    Const x As Long = 995
    Dim i As Long, sh As Worksheet
    Set sh = ActiveSheet
    l = sh.Cells.SpecialCells(xlCellTypeLastCell).Row
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For i = 1 To WorksheetFunction.RoundUp(l / x, 0)
        With Workbooks.Add.Sheets(1)
            sh.Cells(1, 1).Offset((i - 1) * x).Resize(x).EntireRow.Copy .Cells(1, 1)
            .Parent.SaveAs "C:\temp\" & "sample" & i & ".xlsx"    'CHANGE HERE TO SUIT YOUR NEEDS
            .Parent.Close False
        End With
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "done"
End Sub
```


----------



## Hiten_123 (Sep 1, 2022)

Colo said:


> Welcome to the board!
> Here's a sample code for you. Place it in a standard module then give it a try.
> 
> 
> ...


thanks, I will try and let u knw the results


----------



## Hiten_123 (Dec 22, 2022)

hey, this is good but problem is I have data upto 10k rows(variable) and have to make too many excel files manually of 995 rows until last row. I want to split in such a way that 
1. It should create a new workbook after every 995 rows each out of 10k rows including header. 
2. Name it and Save it in designated path with name "_*Prematching MMDDYYYY_v1, v2, v3*_.." and so on

Thank you in advance for your assistance


----------



## Hiten_123 (Dec 23, 2022)

Hiten_123 said:


> thanks, I will try and let u knw the results





Hiten_123 said:


> thanks, I will try and let u knw the results


hey, this is good but problem is I have data upto 10k rows(variable) and have to make too many excel files manually of 995 rows until last row. I want to split in such a way that
1. It should create a new workbook after every 995 rows each out of 10k rows including header.
2. Name it and Save it in designated path with name "_*Prematching MMDDYYYY_v1, v2, v3*_.." and so on

Thank you in advance for your assistance


----------

