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 |
