Excel Macro to split large files into multiple small files

If there is a scenario of migrating the data from one system to other system, the data load will be huge and while loading the huge record in CPI will lead to timeout issue/performance issue. So we would need to split up the files into several small files and then load into the system.

For this scenario, splitting the files, we use excel macro for splitting the file.

1.Open the huge data excel and save it.
2.Then Alt+F11 key - Microsoft Visual Basic Application screen opens
3. Go to Insert menu - Module, then paste the below code in the pop up screen.

Script:

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 = 10000 'how many rows (incl. header) in new files?
Prefix = "SCI_user_deletion_input_QA" '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 &".csv",FileFormat:=xlCSV
wb.Close


'Increment file counter
WorkbookCounter = WorkbookCounter + 1
Next p


Application.ScreenUpdating = True
Set wb = Nothing
End Sub

---------------------------------------------------------------------------

4. Save and close it.
5. Again back to excel sheet -then press Alt+F8.

Now you can find the files in the folder. where you save the (huge data) excel.


--- If really helpful, just give some feedback ---