Split excel workbook ( file ) in multiple workbooks ( files ) in VBA

Split excel file into multiple file VBA

Objective

To split excel workbook or file in multiple workbooks or files in VBA.

Approach

In this VBA Excel automation, we are creating a separate workbook [or excel file ] from each worksheet of a excel file. In order to achieve this goal, we are selecting the source excel file by dialog box. After that we loop through each worksheet in the source excel file and, for every iteration we add a new workbook.

After that, during each iteration, we copy the content of each sheet and paste it on the recently added workbook. Once the content is pasted on the newly added workbook, we save the newly added workbook. Saved workbook will have the name, as that of the worksheet from which the content was copied.

Let’s say if the worksheet’s  name was “Year_2018”, then its split file will also have the name as Year_2018.xlsx. This file will be saved at the same location where original excel file was located.

 

Steps

  1. Open the source excel file by dialog box
  2. Storing the name of selected workbook and the path where this file is located
  3. Looping through each sheet in the selected workbook
  4. Add a new workbook in each iteration, this workbook will be used to copy the data from a single worksheet.
  5. Copy the contents selected worksheet from the source file, and paste it into newly added workbook
  6. Save the newly added workbook at the same location where originally selected file was located.
  7. 7.Continue with the loop
  8. Exit the subroutine once all the sheets have been saved as separate file

 

Code

Sub SpliExcelFile()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim SourceExcelFile As Variant
Dim MyWorkBook As String
Dim MyWorkbookPath As String
Dim NewSplitWorkbook As String
Dim wSheet As Worksheet

'Open only excel files using dialog box
SourceExcelFile = Application.GetOpenFilename(FileFilter:="Excel Files,*.xlsx")
Workbooks.Open (SourceExcelFile)

'Storing the name of selected workbook and it's path for easy navigation
MyWorkBook = ActiveWorkbook.Name
MyWorkbookPath = ActiveWorkbook.Path

'Looping through each worksheet in selected  file
For Each wSheet In ActiveWorkbook.Sheets
    
    'Create a workbook for each sheet
    NewSplitWorkbook = Workbooks.Add.Name
    
    'Copy data from source file
    Windows(MyWorkBook).Activate
    wSheet.Cells.Copy
    
    'Paste data in target split file
    Workbooks(NewSplitWorkbook).Activate
    Sheets("sheet1").Paste
    Range("A1").Select
    
    'Saving the newly split workbook at the same location where original file was located
    Workbooks(NewSplitWorkbook).SaveAs filename:=MyWorkbookPath & "\" & wSheet.Name
    Workbooks(wSheet.Name).Close

Next wSheet

'Close source excel  file
Windows(MyWorkBook).Close

End Sub

 

 

We have created a command button on macro file (.xlsm), this  button is assigned the above shown VBA code.

 

Split excel file into multiple file VBA

 

Before executing the VBA code, we had a excel file, which had 3 worksheets for year 2017,2018,2019. This workbook contains dummy data.

 

Split excel file into multiple file VBA

In  the sample excel workbook , which we intend to split, we have 3 sheets as we can see in the image below.

 

Split excel file into multiple file VBA

 

Once the code is executed, we get 3 more excel file 2017.xlsx,2018.xlsx,2019.xlsx as output files. Worksheet 2017 is saved as 2017.xlsx.Similarly other 2 output files are named on the basis of source worksheet name.

 

Split excel file into multiple file VBA

Notes

  1. We can also save all the sheets as pdf document (.pdf) using this method.
  2. We can also save all the sheets on single pdf file.

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.application.getopenfilename
https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

Post you may like

Convert or save multiple worksheet of excel file as a pdf file – debugvba.com