How to Split Each Excel Sheet Into a Separate File by One Click

1 Introduction

Here in this article, I have explained How can we split each Excel Sheet into a Separate File by doing a single click. Some of my subscribers were searching the solution to this problem, but they are unable to do this. He has a file where it has 100 Excel Sheet in a Single file, now he was asking that can we convert all the sheets and their data also in Single-Single file automatically, I said yes you can do it with an easy single click but we need a VBA code for automatically doing this task.

2 Purpose

We can separate the sheet into single files manually also but it is very time taking work. if someone has 1000 Excel Sheets in a File and wanted to convert All 1000 Sheets into Single- Single file separately and do it manually then it will take almost 2 days to convert. So our purpose to is how to do this task within 1 or 2 minutes.

3 Command Used

Here to Solve this problem we have used a VBA code which I have given below, you just copy & paste into your excel VBA application. Just Open your file then Press “ALT+F11” to open VBA Application by shortcut then Go to the Insert Tab then click on the “Module” button. Now Copy the below code and paste in the Module blank page. Now finally you have to Run the code by pressing the F5 button or directly clicking in Run button on top of the menu. and now you all sheets will start to convert in  Single- Single file separately and automatically. remember – Your all files will be saved where your big file is already saved. means the same path you have to check to find the exported files.

VBA for Split Each Excel Sheet Into a Separate File

Sub SplitEachWorksheet()
Dim FPath As String
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
ws.Copy
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Download VBA Code

 

If the above code is showing an error then try this alternative code

One of my subscribers told me that he applied the above code on his data to split all sheets into Separate files, but it is showing an error “ws.Copy” Try this VBA Code

Sub SplitAllWorksheet()
    Dim FPath As String
    FPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim ws As Worksheet
    Dim newWorkbook As Workbook
    For Each ws In ThisWorkbook.Sheets
        Set newWorkbook = Workbooks.Add ' Create a new workbook
        
        ' Copy data and formatting to the new workbook
        ws.Cells.Copy
        newWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        ' Save and close the new workbook with the sheet's name
        newWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
        newWorkbook.Close False
    Next ws
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Download VBA Code