Excel Macro to Split Data into Multiple Files (With Simple Steps)

This article illustrates how to use Excel macro to split data into multiple files. It will be helpful for you if you need to split data from a large dataset into multiple workbooks. You can use simple code in the VBA editor to get that done easily. Have a quick look through the article to see how to do that.

Excel Macro to Split Data into Multiple Files: With Simple Steps

At first, imagine you have the following dataset. It contains the sales made by employees in the last quarter of 2021 and the first quarter of 2022.

Now assume the path of the master workbook is as shown in the following picture.

Now you want to split the data into multiple workbooks so that each file contains the sales from different years. Then follow the steps below.

Steps to Create the Excel Macro to Split Data into Multiple Files

📌 Open the VBA Editor in Excel

First, press ALT+F11 to open the VBA editor in Excel.
Then 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 of the created files.

📌 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.

Run the VBA Code

Finally, press F5 to run the code. Alternatively, you can press ALT+F8 to open the macro window as shown below. Then run the code from there.

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

After that, open the files. Then you will see the data 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

Conclusion

Now you know how to create a macro in Excel to split data into multiple files. Please let us know if this article has helped you to solve your problem. You can use the comment section below for further queries or suggestions. Stay with us and keep learning.