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

Cool Tips and Tricks Directly Delivered in your Inbox. Subscribe to our Newsletter.

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

Talentnett will use the information you provide on this form to be in touch with you and to provide updates and marketing.