Split large excel file by number of rows

Question

I have a large excel file with about 3000 rows.

I would like to split this data into groups of 100 rows

Is there a command in excel that can help me split this data into different

sheets or files for every 100th row?

Answer 1

Nice solution in this answer to another question on similar problem: https://stackoverflow.com/a/18001183

However, I needed to split the file without adding headers to each new file, just the data rows. My modified / simplified code:

Sub Test()
  Dim wb As Workbook
  Dim ThisSheet As Worksheet
  Dim NumOfColumns As Integer
  Dim RangeToCopy As Range
  Dim WorkbookCounter As Integer
  Dim RowsInFile
  Dim Prefix As String

  Application.ScreenUpdating = False

  'Initialize data
  Set ThisSheet = ThisWorkbook.ActiveSheet
  NumOfColumns = ThisSheet.UsedRange.Columns.Count
  WorkbookCounter = 1
  RowsInFile = 100                   'how many rows (incl. header) in new files?
  Prefix = "test"                    'prefix of the file name

  For p = 1 To ThisSheet.UsedRange.Rows.Count Step RowsInFile
    Set wb = Workbooks.Add

    'Paste the chunk of rows for this file
    Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 1, NumOfColumns))
    RangeToCopy.Copy wb.Sheets(1).Range("A1")

    'Save the new workbook, and close it
    wb.SaveAs ThisWorkbook.Path & "\" & Prefix & "_" & WorkbookCounter
    wb.Close

    'Increment file counter
    WorkbookCounter = WorkbookCounter + 1
  Next p

  Application.ScreenUpdating = True
  Set wb = Nothing
End Sub