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
Many thanks for this Sir.