Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Create new workbooks from each and every worksheet in your workbook | Excel VBA

In an earlier post I explained how to create a workbook from a particular sheet in Excel VBA. Today I will teach you how to create new workbooks from each and every worksheet of your workbook and how to save them to desired location. So here is the workbook I have.


Name of this workbook is “Original file.xlsm”. As you can see this workbook has three worksheets. So I’m going to create 3 separate workbooks from them and save them in folder where this “Original file.xlsm” is in. This is how the folder look like before run the macro. It has only one file.

So below is the full code to separate all sheet to different workbooks and save them.

Sub CreateWorkbooks()

Dim WB As Workbook

Dim WS As Worksheet

For Each WS In Worksheets

   WS.Copy

   Set WB = ActiveWorkbook

   WB.SaveAs ThisWorkbook.Path & "\" & WS.Name, FileFormat:=52

   WB.Close

Next WS

End Sub

Here is the result after running above macro. As you can see three files have created with original sheet names.


Next I will explain about this code in detail. First we define our variables

Dim WB As Workbook

Dim WS As Worksheet

Next we need to loop through all the sheets of our workbook.

For Each WS In Worksheets

Next WS

As you can see there are few other lines between above two lines. So those commands will be executed for each and every worksheet. Following commands will create a new workbook from each sheet.

WS.Copy

Set WB = ActiveWorkbook

Now let's take a closer look at the following line.

WB.SaveAs ThisWorkbook.Path & "\" & WS.Name, FileFormat:=52

What this line does is, it save each workbook in .xlsx format in the folder where we have our original file. ThisWorkbook.Path gives the location of the folder. And WS.Name set the name of the workbook. So here each file is saved with it’s original sheet name. And if you want to save the files in a different location you can replace the ThisWorkbook.Path with desired folder path. Here is an example.

WB.SaveAs "C:\Users\EVS\Documents\" & WS.Name, FileFormat:=52

Also FileFormat number specifies the format when saving the file. Here is the list of excel file formats.

XlFileFormat Enumeration (Excel)

And following line is used to close each newly created workbook.

WB.Close

Contact Form

Name

Email *

Message *