VBA Routine to Add and Name Worksheets
In this Article
- Add Sheet
- Add Sheet with Name
- Add Sheet Before / After Another Sheet
- Add Sheet to Variable
- More Add Sheet Examples
- VBA Coding Made Easy
This tutorial will discuss how to add / insert worksheets using VBA.
This simple macro will add a Sheet before the ActiveSheet:
Sub Add () Sheets.Add End Sub
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).
ActiveSheet.Name = "NewSheet"
Add Sheet with Name
You can also define a Sheet name as you create the new Sheet:
Sheets.Add.Name = "NewSheet"
Create New Sheet with Name from a Cell
Or use a cell value to name a new Sheet:
Sheets.Add.Name = range("a3").value
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:
Sheets.Add(After:=Sheets("Input")).Name = "NewSheet"
Notice the extra parenthesis required in the second example (the first example will generate an error if the second parenthesis are added).
Sheets.Add(Before:=Sheets("Input")).Name = "NewSheet"
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:
Sheets.Add(Before:=Sheets(1)).Name = "FirstSheet"
Add Sheet to Variable
This code assigns the new Sheet to a variable as the sheet is created:
Dim ws As Worksheet Set ws = Sheets.Add
From here you can reference the new sheet with the variable ‘ws’:
ws.name = "VarSheet"
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.
VBA Programming | Code Generator does work for you!
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.
Private Sub CommandButton1_Click() Call CreateWorksheets(Sheets("Sheet2").Range("A1:a10")) End Sub Sub CreateWorksheets(Names_Of_Sheets As Range) Dim No_Of_Sheets_to_be_Added As Integer Dim Sheet_Name As String Dim i As Integer No_Of_Sheets_to_be_Added = Names_Of_Sheets.Rows.Count For i = 1 To No_Of_Sheets_to_be_Added Sheet_Name = Names_Of_Sheets.Cells(i, 1).Value 'Only add sheet if it doesn't exist already and the name is longer than zero characters If (Sheet_Exists(Sheet_Name) = False) And (Sheet_Name <> "") Then Worksheets.Add().Name = Sheet_Name End If Next i End Sub
Function Sheet_Exists(WorkSheet_Name As String) As Boolean Dim Work_sheet As Worksheet Sheet_Exists = False For Each Work_sheet In ThisWorkbook.Worksheets If Work_sheet.Name = WorkSheet_Name Then Sheet_Exists = True End If Next End Function
So if we have the following text in cells A1:A30 in Sheet 2:
Then the following sheets will be created:
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!