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

How to Create a New Workbook from Existing Excel Sheet in Excel VBA

In this post I will share a quick tip with you. Sometimes we need to create a new workbook from existing worksheet in Excel VBA. This existing worksheet can be active worksheet or any other worksheet. Assume we have a workbook like this.

This workbook contains 3 sheets. Think we need to create a new workbook from “Sheet3”. We can do that using following code.

Sub CreateNewWorkbook() Dim WB As Workbook Dim WS As Worksheet Set WS = Worksheets("Sheet3") WS.Copy Set WB = ActiveWorkbook End Sub

If you run above macro you will notice a new workbook created from Sheet3 like this.

Now we can see two workbooks in taskbar. Our original workbook and the newly created one.

And if you want to create a new workbook from active sheet you can use below code.

Sub CreateNewWorkbookFromActiveSheet() Dim WB As Workbook Dim WS As Worksheet Set WS = ActiveSheet WS.Copy Set WB = ActiveWorkbook End Sub

If you want to learn how to create workbooks from each and every sheet of the workbook and save them to a desired folder, then check this post.

Create new workbooks from each and every worksheet in your workbook

How to Sum Values in Excel

There are many ways to sum values in Excel. If you want you can directly enter the values you want to sum like this.

Also you can select a cell and then type in the formula bar too. Because if the cell is small then you can’t see all the values you entered to the cell. But formula bar has lot more space. So you don’t need to resize cells when enter long formulas.

And if you want you can enter the cell addresses of the values you want to sum instead of directly entering the values. Using this method you can change the values in the cells and get the sum instantly without doing any change to the formula.

Actually you don’t need to type the cell addresses manually. You can select the cells using mouse while pressing + key from the keyboard.

Also you can use inbuilt function in Excel to sum values in ranges. If this is a continuous range you can use something like this.

Here again, you don’t need to type the ranges manually. You can first type =Sum(
Then you can select the range using mouse. So the range will be inserted automatically to the formula. Finally you can close the bracket and press enter.

And if it is not a continuous range you can use a formula like this.