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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With