To split excel workbook or file in multiple workbooks or files in VBA.
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.
- Open the source excel file by dialog box
- Storing the name of selected workbook and the path where this file is located
- Looping through each sheet in the selected workbook
- Add a new workbook in each iteration, this workbook will be used to copy the data from a single worksheet.
- Copy the contents selected worksheet from the source file, and paste it into newly added workbook
- Save the newly added workbook at the same location where originally selected file was located.
- 7.Continue with the loop
- Exit the subroutine once all the sheets have been saved as separate file
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.
Before executing the VBA code, we had a excel file, which had 3 worksheets for year 2017,2018,2019. This workbook contains dummy data.
In the sample excel workbook , which we intend to split, we have 3 sheets as we can see in the image below.
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.
- We can also save all the sheets as pdf document (.pdf) using this method.
- We can also save all the sheets on single pdf file.
Post you may like