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

Get FREE Advanced Excel Exercises with Solutions!

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


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.

Excel Macro to Split Data into Multiple Files


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

Excel Macro to Split Data into Multiple Files

  • 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

Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo