In this guide, we’re going to show you how to create and name a worksheet with VBA in Excel.
Syntax
You can create and name a worksheet using the Sheets.Add and Worksheets.Add methods. Both methods work similarly and have 4 optional arguments and return a sheet object which can take a Name property.
Name | Description |
Before | Optional. The sheet before which the new sheet is to be added. If omitted, Excel creates the new sheet(s) before the selected sheet(s). |
After | Optional. The sheet after which the new sheet is added. If omitted, Excel creates the new sheet(s) before the selected sheet(s). |
Count | The number of sheets to be added. The default is the number of selected sheets. |
Type | Specifies the sheet type. The default is xlWorksheet which represents a standard worksheet. |
Adding a single sheet
All arguments are optional. The method without arguments creates worksheet(s) equal to the number of selected worksheets before the first selected worksheet.
Sheets.Add
For example, if two sheets are selected, the method will add two worksheets. To ignore the selected sheets and set the sheet number to one (1), use 1 for the Count argument.
Sheets.Add Count:=1
Adding multiple sheets
You can set the Count argument to an integer greater than 1 to add multiple sheets at once. For example, the following code adds three (3) worksheets.
Sheets.Add Count:=3
Adding a sheet with a name
Sheets.Add method returns a sheet object and sets its name by updating the Name property. A property is an attribute of object that determines one of the object’s characteristics. The property of an object is addressed by entering the property name after the corresponding object and a dot(.). Just like calling the Add method for the Sheets object.
If all you need is to create worksheets and name them regardless of their position, use one of the following code lines.
Sheets.Add.Name = “My Sheet” Sheets.Add(Count:=1).Name = “My Sheet” ‘Use this line to ensure creating a single sheet
Adding a sheet before or after a specific sheet
If the new sheet's position is important, use either the Before or After argument. Each argument accepts a sheet object. The new sheet will be created before or after the sheet you supplied based on the argument you are using.
You can call a sheet object by giving the sheet’s name or index to Sheets or Worksheets objects. It can be a variable which you have defined. Here are some examples:
Sheets.Add Before:=Worksheets("My Sheet") ‘Add sheet(s) before “My Sheet” Sheets.Add After:=Worksheets(3) ‘Add sheet(s) after the third sheet
Dim ws As Worksheet ‘Define a new worksheet object Set ws = Sheets.Add ‘Create and assign new sheet to the worksheet object Sheets.Add After:=ws ‘Add a new sheet after the recently added sheet (ws)
Creating and naming multiple worksheets
To name multiple worksheets, you have to use an array of names and a loop. Let’s say you have names in a range like A1:A5 in the worksheet named “Sheet1”. A loop should check each cell inside the range and create a worksheet from the corresponding name. Check out the following code:
Sub CreateAndNameMultipleSheets() Dim rng As Range 'Range object which defines a cell For Each rng In Sheets("Sheet1").Range("A1:A5") Sheets.Add.Name = rng.Value Next End Sub
This is our last tip for how to create and name a worksheet with VBA in Excel article. If you are new to loops in VBA, check out All You Need to Know on How to Create a VBA loop in Excel to understand and find more ways of looping.