How to create multiple files using sheets in Excel VBA

How to create multiple files using sheets in Excel VBA

How many times you come across a situation where you want to create multiple files using sheets in Excel, I believe many times, but manually saving each worksheet as a separate file. The wait is over, you can automate this process by using Excel VBA.

Here is the solution!

VBA Code

Sub CreatNewFile()

'Talentnett Solutions - visit us at https://talentnett.com/'

Dim ws As Worksheet
Dim NewFile As Workbook
Dim FileName As String

On Error GoTo ErrorHandler

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Sheets
FileName = ThisWorkbook.Path & "/" & ws.Name
ws.Copy
ActiveWorkbook.SaveAs FileName
ActiveWorkbook.Close

Next ws

Application.ScreenUpdating = True

ErrorHandler:
Exit Sub

End Sub

#Step 1 – Go to Excel

#Step 2 – Right-click on the sheet tab>>View code or Press ALT+F11

#Step 3  – Paste the above code in the VBA window and Press Alt+Q

paste macro code here

#Step 4 – Save your excel file as a macro-enabled file (Press “F12” >> change Save Type as “Excel Macro-Enabled Workbook”)

#Step 5 – Go to Blank sheet >> Insert any shape – Insert>>Shapes

#Step 6 – Right-click on that shape and Assign that macro [CreateNewFile]

#Step 7 – Click on the shape

Check out the folder, hola! all the sheets have been converted into a new excel file.

Read More:

How to replace other cell values in excel using VBA

How to Convert All Cell Values (Text) to UPPER Case VBA

COMMENTS