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.

Download Practice Workbook

You can download the practice workbook from the download button below.

How to Create an Excel Macro to Split Data Into Multiple Files

• 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

📌 Turn Off Screen Updating

Sub ExcelMacroToSplitDataToMultipleFiles()
Application.ScreenUpdating = False
End Sub

📌 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

📌 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

📌 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

📌 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

📌 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

📌 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

📌 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

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.

Read More: How to Split Data in Excel (5 Ways)

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.

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. Do visit our ExcelDemy blog to read more on excel. Stay with us and keep learning.

Further Readings

• How to Split Comma Separated Values into Rows or Columns in Excel
• Split Data in One Excel Cell into Multiple Columns (5 Methods)
• How to Split Data into Equal Groups in Excel (3 Methods)
• Excel Macro to Split a Cell into Multiple Rows (With Easy Steps)
• How to Split Data from One Cell into Multiple Rows in Excel (3 Methods)
• Split Data into Multiple Worksheets in Excel