Easy way to take a file and make it 2,000 rows per tab?

[DWightman]

Is there an easy way to take a file I have say 6,700 (or more) rows of data in... And run some sort of macro, or formula on it that would automatically take it and break it into tabs that each only had 2,000 rows of data on them? In a perfect world, keeping the header rows the same names and same order of columns too?

 

[Logit]

Sub SplitData()
'Updateby20140617
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
SplitRow = Application.InputBox("Split Row Num", xTitleId, 5, Type:=1)
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (WorkRng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
    xRow.Resize(resizeCount).Copy
    Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

How to split data into multiple worksheets by rows count in Excel?

 

[DWightman]

Thank you for your assistance and time. I would copy and paste this in exactly as it is? Or are the pieces you have in certain colors things I would need to update to be specific to my file?
Sorry, I have never used VBA before so maybe that is a dumb question. I know it exists and I know it can do some awesome stuff. But I am not that far yet!

 

[Logit]

Here is the edited version of the previous macro so it works for your needs of 20,000 rows :

Option Explicit

Sub SplitData()
'Updateby20140617
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
Dim i As Long
Dim resizeCount
On Error Resume Next

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", , WorkRng.Address, Type:=8)
SplitRow = Application.InputBox("Split Row Num", , 20000, Type:=1)
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)

Application.ScreenUpdating = False
    
    For i = 1 To WorkRng.Rows.Count Step SplitRow
        resizeCount = SplitRow
        If (WorkRng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
        xRow.Resize(resizeCount).Copy
        Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
        Application.ActiveSheet.Range("A1").PasteSpecial
        Set xRow = xRow.Offset(SplitRow)
    Next
    
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

This code should be pasted into a MODULE in the VBE. Here are instructions to create a macro : Create a Macro in Excel
When you paste the COMMAND BUTTON on the SHEET1 of your workbook, a small window will appear with the names of the various macros you have in
your workbook. Be certain you select macro named SplitData.

Download sample workbook : Internxt Drive

 

[jolivanes]

Have as many rows as you want. Change references where required.

Sub Or_So_Maybe()
Dim lc As Long, lr As Long, i As Long, j As Long, rws As Long, sh1 As Worksheet
Set sh1 = Worksheets("Sheet1")    '<----- Change to sheet name that has all the data
lc = sh1.Cells(1, sh1.Columns.Count).End(xlToLeft).Column
rws = 2000    '<----- Number of rows per sheet. Change as desired.
lr = Application.WorksheetFunction.Ceiling(sh1.Cells(sh1.Rows.Count, 2).End(xlUp).Row, rws) + 1
j = 2
    For i = 1 To Int(lr / rws)
    ThisWorkbook.Worksheets.Add(, Sheets(Sheets.Count)).Name = "New Sheet " & i
            With Sheets(Sheets.Count)
                .Cells(1, 1).Resize(, lc).Value = sh1.Cells(1, 1).Resize(, lc).Value
                .Cells(2, 1).Resize(rws - 1, lc).Value = sh1.Cells(j, 1).Resize(rws - 1, lc).Value
        End With
        j = j + rws - 1
    Next i
End Sub

 

[Logit]

Edit this line :

SplitRow = Application.InputBox("Split Row Num", , 20000, Type:=1)

To :

SplitRow = Application.InputBox("Split Row Num", , 2000, Type:=1)

I mistakenly set the number of rows to twenty-thousand instead of two-thousand. Sorry.