Create a New Sheet in VBA

Many times, we need to add a new sheet to a workbook. You can use Excel VBA or the keyboard shortcut to add a new sheet or sheets to a workbook. The sheet can be a worksheet, macro sheet, or chart sheet. This tutorial focuses on creating new worksheets in an Excel workbook.

Example

We will use the following workbook in our illustrations.

Create one or more worksheets using Excel VBA

We will give examples of how to add a worksheet or worksheets to a workbook using Excel VBA.

Example 1: Add a single worksheet after another worksheet

We use the following steps:

1. Open the workbook to which you want to add a worksheet.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Click Insert >> Module to create a new module.

4. Type the following procedure in the module.

5. Save the procedure and save the workbook as an Excel Macro-Enabled Workbook.
6. Place the cursor anywhere in the procedure and press F5 to run the code.
7. Press Alt +F11 to switch back to the active worksheet.

A new worksheet named Europe has been added to the workbook. The new worksheet has been inserted after the Africa worksheet and is now the active worksheet.

Example 2: How to add a worksheet before another worksheet

Suppose we want to add a worksheet before the Asia worksheet.

We proceed as follows:

1. Press Alt + F11 to open the Visual Basic Editor.
2. Insert a new module and type in the following procedure.

3. Place the cursor anywhere in the procedure and press F5 to run the code.
4. Press Alt + F11 to switch back to the active worksheet.

A new worksheet named Instructions has been created and added before the Asia worksheet.

Note: The following alternate procedure will also add the worksheet named Instructions before the Asia worksheet. The Asia worksheet is at the beginning of the workbook and occupies position 1 in the sheets collection:

You can use the procedure that you are comfortable with.

Example 3: How to add a sheet to the end of the workbook

We may also want to add a worksheet to the end of the workbook.

We use the following steps:

1. Press Alt + F11 to open the Visual Basic Editor.
2. Insert a new module and type in the following procedure:

3. Place the cursor anywhere in the procedure and press F5 to run the code.
4. Press Alt + F11 to switch back to the active worksheet.

The new worksheet named Addendum has been added to the end of the workbook.

Example 4: How to add many worksheets to a workbook

We add four more worksheets to the workbook. We get the names of the worksheets from range B5:B8 in the dataset in the Summary worksheet.

We use the following steps:

1. Select the Summary worksheet to make it the active worksheet.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Click Insert >> Module to insert a new module.
4. Type the following procedure in the module.

5. Save the procedure and save the workbook as an Excel Macro-Enabled Workbook.
6. Place the cursor anywhere in the procedure and press F5 to run the code.
7. Press Alt + F11 to switch back to the active worksheet.

Four worksheets are added to the workbook to the left of the Summary worksheet.

One advantage of using Excel VBA is that with a single click you can add many worksheets and specify where you want them inserted.

Explanation of the procedures

The procedures utilize the Add method of the Sheets object to create new worksheets.

The procedures also utilize the Name property of the Sheet object to assign names to the created worksheets.

The syntax of the method is as follows:

Sheets.Add ([Before], [After], [Count], [Type])

The method has four optional arguments:

• Before. Use this argument to add a sheet before a specified sheet. If this argument is omitted, the new sheet is inserted before the active sheet.
• After. It is for adding a sheet after a specified sheet. If this argument is omitted, the new sheet is added before the active worksheet.
• Count. It gives the number of sheets to add. If this argument is omitted, only one new sheet is added.
• Type. It specifies the type of sheet to add. It can be a worksheet, chart sheet, or macro sheet. If this argument is omitted, a worksheet is inserted.

If all the arguments are omitted, Excel adds one worksheet to the left of the active sheet.

Create one or more worksheets using the keyboard shortcut

We use a copy of the workbook we used in the previous section in our illustrations in this section.

Example 1: How to add one worksheet

We use the following steps:

1. Open the workbook to which we want to add sheets.

2. Press and hold down the Shift key and press and release key F11. A new worksheet is added to the workbook.

Notice that the worksheet is added to the left of the active worksheet.

Use the keyboard shortcut Shift + F11 again to add another worksheet.

Note: The new worksheet name is incremental to the previous worksheet name. In this case, Sheet2 follows Sheet1. But if for example, we delete Sheet2 and add a new worksheet using the keyboard shortcut the new worksheet becomes Sheet3 and not Sheet2.

This is because Excel keeps count of how many worksheets are inserted.

Example 2: Add many sheets using the keyboard shortcut

We will demonstrate how to add three worksheets to the workbook below.

We use the following steps:

1. Press and hold down the Ctrl and Shift keys.
2. Press and release the Page Down key twice to select two worksheets to the right of the active worksheet. Three worksheets are now selected:

3. Press Shift + F11 to insert three new worksheets.

Conclusion

Many times, we are required to add a new sheet to a workbook. This tutorial looked at how to add worksheets to a workbook using Excel VBA and a keyboard shortcut.