We usually talk about Power Platform here, but this time we bring you some awesome VBA piece of code that you use to split the content of an Excel worksheet into multiple files, based on N number of rows – created by our colleague Jeymi Membreño.
Say goodbye to manual splitting and hello to automation using VBA in Excel.
If you have a file with 550 rows and you want to get separate files for each batch of 100 rows, this code will return 6 files.
Step 1. Let’s Define Some Variables
' Variables
Dim ws As Worksheet
Dim totalRows As Long
Dim rowsPerFile As Long
Dim totalFiles As Long
Dim i As Long
Dim startingRow As Long
Dim endingRow As Long
Dim newWorkbook As Workbook
' Set Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
' Number of rows per file
rowsPerFile = 100
' Set starting row. We have a header row so we want to start copying and pasting from this row.
startingRow = 2
Step 2. Calculate The Number Of Rows In Worksheet And The Number Of Files That The Code Will Return
' Get number of rows in file
totalRows = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Calculate the number of files needed – here we use the result from previous line and the rowsPerFile variable to calculate.
totalFiles = Application.WorksheetFunction.Ceiling(totalRows / rowsPerFile, 1)
Step 3. Copy Data And Create Each Individual File
' Loop to create files
For i = 1 To totalFiles
' Calculate row range for each file
endingRow = startingRow + rowsPerFile - 1
If endingRow > totalRows Then
endingRow = totalRows
End If
' Create new book
Set newWorkbook = Workbooks.Add
' Copy header row in new file
ws.Rows(1).EntireRow.Copy newWorkbook.Sheets(1).Rows(1)
' Copy rows to new file
ws.Rows(startingRow & ":" & endingRow).EntireRow.Copy newWorkbook.Sheets(1).Rows(2)
' Change workseet name
newWorkbook.Sheets(1).Name = ws.Name
' Save file in the same workbook path as current file – files will be created with the word “File” as prefix and then the number of file.
newWorkbook.SaveAs ThisWorkbook.Path & "\" & "File" & i & ".xlsx" '
' Close new book
newWorkbook.Close SaveChanges:=False
' Update starting row for next file
startingRow = endingRow + 1
Next i
That’s it!
Just replace the rowsPerFile variable if you need more or less than 100 rows to be copied over to the individual files.
This Is How The Final Code Looks
Sub SplitSheetContent()
' Variables
Dim ws As Worksheet
Dim totalRows As Long
Dim rowsPerFile As Long
Dim totalFiles As Long
Dim i As Long
Dim startingRow As Long
Dim endingRow As Long
Dim newWorkbook As Workbook
' Set Worksheet
Set ws = ThisWorkbook.Sheets("Events")
' Number of rows per file
rowsPerFile = 100
' Get number of rows in file
totalRows = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Calculate the number of files needed
totalFiles = Application.WorksheetFunction.Ceiling(totalRows / rowsPerFile, 1)
' Init variables
startingRow = 2
' Loop to create files
For i = 1 To totalFiles
' Calculate row range for each file
endingRow = startingRow + rowsPerFile - 1
If endingRow > totalRows Then
endingRow = totalRows
End If
' Create new book
Set newWorkbook = Workbooks.Add
' Copy header row in new file
ws.Rows(1).EntireRow.Copy newWorkbook.Sheets(1).Rows(1)
' Copy rows to new file
ws.Rows(startingRow & ":" & endingRow).EntireRow.Copy newWorkbook.Sheets(1).Rows(2)
' Change workseet name
newWorkbook.Sheets(1).Name = ws.Name
' Save file in the same workbook path as current file
newWorkbook.SaveAs ThisWorkbook.Path & "\" & "Reporte00" & i & ".xlsx" '
' Close new book
newWorkbook.Close SaveChanges:=False
' Update starting row for next file
startingRow = endingRow + 1
Next i
End Sub
