Excel Macro to Split Data into Multiple Files: 2 Methods

Method 1 – Steps to Create the Excel Macro to Split Data into Multiple Files

Open the VBA Editor in Excel

Press ALT+F11 to open the VBA editor in Excel.
Select Insert >> Module as shown in the following picture.

Create Sub Procedure

Sub ExcelMacroToSplitDataToMultipleFiles()

End Sub

We will write the code in this sub procedure.

Turn Off Screen Updating

Sub ExcelMacroToSplitDataToMultipleFiles()
Application.ScreenUpdating = False
End Sub

Application.ScreenUpdating = False : If screen updating is enabled, excel will show the processes run by the macro. By setting the value to False, the macro will run in the background making it work faster. We will need to reset the default True value at the end.

Declare Necessary Variables

Sub ExcelMacroToSplitDataToMultipleFiles()
Application.ScreenUpdating = False
Dim MasterWorkbook, NewWorkbook As Workbook
Dim Dataset, NewWorksheet As Worksheet
Dim Rng As Range
Dim i As Integer
End Sub

The MasterWorkbook variable will refer to the source file. The NewWorkbook variable will be used to create new spreadsheets. Dataset refers to the worksheet in the master workbook. The Rng variable will be used to split data from different ranges of the master workbook.

Set the Master Workbook

Sub ExcelMacroToSplitDataToMultipleFiles()
Application.ScreenUpdating = False
Dim MasterWorkbook, NewWorkbook As Workbook
Dim Dataset, NewWorksheet As Worksheet
Dim Rng As Range
Dim i As Integer
Set MasterWorkbook = ActiveWorkbook
Set Dataset = MasterWorkbook.Sheets("Sales")
End Sub

This determines the active workbook as the master workbook or the source file.

For Loop for Multiple Workbook

Sub ExcelMacroToSplitDataToMultipleFiles()
Application.ScreenUpdating = False
Dim MasterWorkbook, NewWorkbook As Workbook
Dim Dataset, NewWorksheet As Worksheet
Dim Rng As Range
Dim i As Integer
Set MasterWorkbook = ActiveWorkbook
Set Dataset = MasterWorkbook.Sheets("Sales")
For i = 1 To 2
Next i
End Sub

We will use the For Loop to split the data into each created file.

Create New Workbook

Sub ExcelMacroToSplitDataToMultipleFiles()
Application.ScreenUpdating = False
Dim MasterWorkbook, NewWorkbook As Workbook
Dim Dataset, NewWorksheet As Worksheet
Dim Rng As Range
Dim i As Integer
Set MasterWorkbook = ActiveWorkbook
Set Dataset = MasterWorkbook.Sheets("Sales")
For i = 1 To 2
Set NewWorkbook = Workbooks.Add
Set NewWorksheet = NewWorkbook.Sheets(1)
Next i
End Sub

Set NewWorkbook = Workbooks.Add will create a new xlsx file.

Split Data into the Files

Sub ExcelMacroToSplitDataToMultipleFiles()
Application.ScreenUpdating = False
Dim MasterWorkbook, NewWorkbook As Workbook
Dim Dataset, NewWorksheet As Worksheet
Dim Rng As Range
Dim i As Integer
Set MasterWorkbook = ActiveWorkbook
Set Dataset = MasterWorkbook.Sheets("Sales")
For i = 1 To 2
Set NewWorkbook = Workbooks.Add
Set NewWorksheet = NewWorkbook.Sheets(1)
Dataset.Range("A1:A20").Copy NewWorksheet.Range("A1")
If i = 1 Then
Set Rng = Dataset.Range("B2:D20")
Else
Set Rng = Dataset.Range("E2:G20")
End If
Rng.Copy NewWorksheet.Range("B2")
Next i
End Sub

Dataset.Range(“A1:A20”).Copy NewWorksheet.Range(“A1”) will split the common data into each files.
Set Rng = Dataset.Range(“B2:D20”) sets the range for sales from the last quarter of 2021.
Set Rng = Dataset.Range(“E2:G20”) sets the range for sales from the first quarter of 2022.
Rng.Copy NewWorksheet.Range(“B2”) will split the varying data into new files.

Save & Close the Created Files

Sub ExcelMacroToSplitDataToMultipleFiles()
Application.ScreenUpdating = False
Dim MasterWorkbook, NewWorkbook As Workbook
Dim Dataset, NewWorksheet As Worksheet
Dim Rng As Range
Dim i As Integer
Set MasterWorkbook = ActiveWorkbook
Set Dataset = MasterWorkbook.Sheets("Sales")
For i = 1 To 2
Set NewWorkbook = Workbooks.Add
Set NewWorksheet = NewWorkbook.Sheets(1)
Dataset.Range("A1:A20").Copy NewWorksheet.Range("A1")
If i = 1 Then
Set Rng = Dataset.Range("B2:D20")
Else
Set Rng = Dataset.Range("E2:G20")
End If
Rng.Copy NewWorksheet.Range("B2")
NewWorkbook.SaveAs Filename:=MasterWorkbook.Path & "\Sales" & i & ".xlsx"
NewWorkbook.Close False
Next i
End Sub

NewWorkbook.SaveAs Filename:=MasterWorkbook.Path & “\Sales” & i & “.xlsx” will save the new file in the same path of the master workbook.
NewWorkbook.Close False will close the new file without saving it as it is already saved.

Turn on Screen Updating

Sub ExcelMacroToSplitDataToMultipleFiles()
Application.ScreenUpdating = False
Dim MasterWorkbook, NewWorkbook As Workbook
Dim Dataset, NewWorksheet As Worksheet
Dim Rng As Range
Dim i As Integer
Set MasterWorkbook = ActiveWorkbook
Set Dataset = MasterWorkbook.Sheets("Sales")
For i = 1 To 2
Set NewWorkbook = Workbooks.Add
Set NewWorksheet = NewWorkbook.Sheets(1)
Dataset.Range("A1:A20").Copy NewWorksheet.Range("A1")
If i = 1 Then
Set Rng = Dataset.Range("B2:D20")
Else
Set Rng = Dataset.Range("E2:G20")
End If
Rng.Copy NewWorksheet.Range("B2")
NewWorkbook.SaveAs Filename:=MasterWorkbook.Path & "\Sales" & i & ".xlsx"
NewWorkbook.Close False
Next i
Application.ScreenUpdating = True
End Sub

Application.ScreenUpdating = True resets screen updating to its’ default value.

Method 2 – Run the VBA Code

Press F5 to run the code. Press ALT+F8 to open the macro window, as shown below. Run the code from there.

Go back to the destination folder. Then you will see two files have been created as follows.

Open the files. The data will be split into those files, as shown in the following picture.

Things to Remember

Excel will show an alert if you rerun the code asking if you want to replace the existing files.
You need to modify the code according to the data ranges in your dataset.

Download Practice Workbook

Split Data into Multiple Files.xlsm