VBA Routine to Add and Name Worksheets

Associated Files Download Links

This tutorial will discuss how to add / insert worksheets using VBA.

Add Sheet

This simple macro will add a Sheet before the ActiveSheet:

After inserting a Sheet, the new Sheet becomes the ActiveSheet. You can then use the ActiveSheet object to work with the new Sheet (At the bottom of this article we will show how to insert a new sheet directly to a variable).

Add Sheet with Name

You can also define a Sheet name as you create the new Sheet:

Create New Sheet with Name from a Cell

Or use a cell value to name a new Sheet:

 

 

 

Add Sheet Before / After Another Sheet

You might also want to choose the location of where the new Sheet will be inserted. You can use the After or Before properties to insert a sheet to a specific location in the workbook.

Insert Sheet After Another Sheet

This code will insert the new sheet AFTER another sheet:

This will insert a new Sheet AFTER another sheet and specify the Sheet name:

Notice the extra parenthesis required in the second example (the first example will generate an error if the second parenthesis are added).

 

or Before:

In these examples we explicitly named the Sheet used to determine the sheet location. Often you’ll want to use the Sheet Index number instead, so that you can insert the sheet to the beginning or end of the Workbook:

Add Sheet To End of Workbook

To add a Sheet to the end of the workbook:

 

Add Sheet To Beginning of Workbook:

To add a Sheet to the beginning of the workbook:

 

Add Sheet to Variable

This code assigns the new Sheet to a variable as the sheet is created:

From here you can reference the new sheet with the variable ‘ws’:

 

More Add Sheet Examples

Create Sheet if it Doesn’t Already Exist

You might want to create a sheet only if it doesn’t already exist.

 

Create Worksheets From List of Names

The following routine will look at the contents of a single column set up Excel worksheets within the current workbook with these names. It makes a call to another function to see if a sheet with that name already exists, and if so the sheet isn’t created.

So if we have the following text in cells A1:A30 in Sheet 2:

adding sheets

Then the following sheets will be created:

adding sheets 2

Note that although “Dog” appears twice, only one sheet is created.

To download the .XLS file for this tutorial, click here.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

 

Learn More!


<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues:

You may also like some of this related content...

This article will discuss the ActiveSheet object in VBA. It will also discuss how to
If your template design requires making Excel look less like Excel, you might want to
Unhide All Worksheets in Workbook The following is a macro to unhide all worksheets in
Worksheet_Change Event You may want to run a macro when a cell changes. A popular
Application.Sheets.Count - Count Worksheets If you ever need to count the number of sheets in
Ads