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