You can use the ‘Sheets.Add’ method in Excel VBA to add one or more sheets to a workbook.
In this tutorial, I will show you several scenarios where you can use VBA’s ‘Sheets.Add’ method to add new sheets to a workbook.
The Sheets.Add Method
The ‘Sheets.Add’ method creates a new worksheet, chart sheet, or macro sheet in a workbook and makes it the active sheet.
The syntax of the method:
expression.Add (Before, After, Count, Type)
expression is a variable representing a Sheets object.
The method takes four optional arguments of Variant data type, and they are described below:
| Argument | Description |
|---|---|
| Before | An object that specifies the sheet before which you want to add the new sheet. |
| After | An object that specifies the sheet, after which you want to add the new sheet. |
| Count | The number of sheets you want to add. The default value is the number of sheets you have selected. |
| Type | Specifies the type of sheet you want to add. It can be any of the following XlSheetType constants: xlChart, xlWorksheet, xlExcel4IntlMacroSheet, or xlExcel4MacroSheet. xlWorksheet is the default value. Specify the template’s path to add a sheet based on an existing template. |
Note: If you omit all four optional arguments, the ‘Sheets.Add’ method will create one new worksheet and insert it before the active sheet.
Scenario #1: Add One New Worksheet to the Current Workbook (Before the Active Sheet)
Suppose you have a workbook with three worksheets:
You can use the VBA code below to add a new worksheet before the active ‘March’ worksheet:
Sub AddNewWorkSheet()
Sheets.Add
End SubWhen you run the code, it creates a new worksheet before the ‘March’ worksheet and makes it the active sheet as depicted below:
Note: Instead of using ‘Sheets.Add’ as shown in the example code, you can use ‘Worksheets.Add’ to achieve the same outcome.
Also read: Delete Sheet Using VBA
Scenario #2: Add a Worksheet Before or After a Specific Sheet and Rename It
Suppose you have a workbook with three worksheets:
The VBA code below creates a new worksheet named ‘January Report’ before the ‘February’ worksheet:
Sub CreateSheetBeforeSpecificSheet()
Dim targetSheetName As String
Dim sheetExists As Boolean
Dim ws As Worksheet
targetSheetName = "February"
sheetExists = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = targetSheetName Then
sheetExists = True
Exit For
End If
Next ws
If sheetExists Then
Worksheets.Add(Before:=Worksheets(targetSheetName)).Name = "January Report"
Else
MsgBox targetSheetName & " not found. Please verify the sheet's name.", vbExclamation
End If
End SubWhen you run the code, it creates the worksheet ‘January Report’ before the ‘February’ worksheet, as depicted below:
The VBA code checks if a worksheet named ‘February’ exists in the workbook.
If it does, it creates a new worksheet before the ‘February’ worksheet and names it ‘January Report.’ If ‘February’ doesn’t exist in the workbook, the code displays a message box indicating it couldn’t find the sheet.
Note: If you want to create a new worksheet after the target worksheet and rename it, modify the fourteenth statement of the code as follows:
Worksheets.Add(After:=Worksheets(targetSheetName)).Name = “January Report”
Also read: VBA to Copy Sheet to New Workbook
Scenario #3: Add a Sheet Before Or After The First Sheet in the Current Workbook
Let’s say you have a workbook with three worksheets:
You can use the VBA code below to create a new worksheet before ‘January,’ the first sheet:
Sub CreateSheetBeforeFirstSheet()
Sheets.Add Before:=Sheets(1)
End SubWhen you execute the code, it creates a new worksheet at the beginning of the existing worksheets, as depicted below:
The code creates a new worksheet and inserts it before the sheet with index number 1, which occupies the first position of all the existing worksheets in the current workbook.
Note: To create a new worksheet after the first sheet in the active workbook, modify the code’s second statement as follows:
Sheets.Add After:=Sheets(1)
Also read: VBA to Check If Sheet Exists
Scenario #4: Add a Sheet After or Before the Last Sheet in the Current Workbook
Suppose you have a workbook with three worksheets:
You can use the VBA code below to create a new worksheet after the last sheet in the workbook:
Sub CreateSheetAfterLastSheet()
Sheets.Add After:=Sheets(Sheets.Count)
End SubWhen you run the code, it creates a new worksheet and inserts it after the last worksheet in the active workbook, as depicted below:
The code uses the ‘Sheets.Count’ property to return the number of all sheets in the workbook.
The number returned by the ‘Sheets.Count’ property is equivalent to the index number of the last sheet in the workbook. The code creates and inserts a new worksheet after the last worksheet, the sheet with the last index number.
Note: To create a sheet before the last sheet in the workbook, you can modify the code’s second statement as follows:
Sheets.Add Before:=Sheets(Sheets.Count)
Also read: VBA to Copy Range to Another Sheet
Scenario #5: Add a Sheet With a Specific Name to the Current Workbook
Suppose you have a workbook with three worksheets:
You can use the VBA code below to add a new sheet named ‘Summary’ to the workbook:
Sub CreateSheetWithName()
Sheets.Add.Name = "Summary"
End SubWhen you run the code, it creates a new worksheet, names it ‘Summary,’ and inserts it before the ‘March’ worksheet, as shown below:
Scenario #6: Add a Sheet to the Current Workbook With the Name From a Cell
Instead of hard-coding the name of a sheet, as in Scenario #5, you can let the code pick the name from a cell.
The code below adds a new worksheet to the current workbook with a name from cell A1:
Sub CreateSheetNameFromCell()
Dim shtName As String
shtName = ActiveSheet.Range("A1").Value
If shtName = "" Then
MsgBox "Cell A1 is empty. Please provide a valid name for the sheet.", vbExclamation
Exit Sub
End If
On Error Resume Next
Sheets.Add(After:=Sheets(Sheets.Count)).Name = shtName
If Err.Number <> 0 Then
MsgBox "Could't create sheet. The name is invalid or already exits in workbook.", vbExclamation
End If
On Error GoTo 0
End SubWhen executed, the code creates a new worksheet in the active workbook using the name specified in cell A1 of the active sheet. The code handles errors for an empty cell or an invalid/existing sheet name.
Scenario #7: Add Multiple Worksheets Before the Active Sheet in the Current Workbook
To add multiple sheets to a workbook, use the ‘Sheets.Add’ method and define the ‘Count’ parameter with the number of sheets to add.
Let’s say you have a workbook with three worksheets:
You can use the VBA code below to create three new worksheets on the left of the ‘March’ active sheet:
Sub AddMultipleWorkSheets()
Sheets.Add Count:=3
End SubWhen you execute the code, it adds three new worksheets to the left of the ‘March’ worksheet, as shown below:
Scenario #8: Add Multiple Worksheets to the Current Workbook With Names From a Cell Range
Suppose you have the list below on the ‘March’ worksheet of the active workbook:
You can use the following VBA code to add multiple worksheets to the current workbook, with names from the list, the cell range A1:A10:
Sub CreateSheetsNamesFromCellRange()
Dim ShtCount As Long
Dim SheetName As String
Dim i As Long
ShtCount = Range("A1:A10").Rows.Count
For i = 1 To ShtCount
SheetName = Sheets("March").Range("A1:A10").Cells(i, 1).Value
If SHEETEXISTS(SheetName) = False And SheetName <> "" Then
Sheets.Add().Name = SheetName
End If
Next i
End Sub
Function SHEETEXISTS(SheetName As String) As Boolean
Dim ws As Worksheet
SHEETEXISTS = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = SheetName Then
SHEETEXISTS = True
End If
Next
End FunctionThe subroutine above initializes variables and loops through each cell in the range A1:A10 on the ‘March’ worksheet.
The code extracts a name from each cell in the cell range. The subroutine uses the SHEETEXISTS user-defined function to check if a sheet with the extracted name already exists in the workbook.
It returns ‘True’ if the sheet exists and ‘False’ otherwise.
If the cell value is not an empty string and the sheet with a name corresponding to the cell value does not exist, the code creates a new sheet with the cell value as its name.
Note: It is crucial to ensure that the values in the cell range are valid worksheet names.
Each value should not exceed 31 characters and should not contain any of the following characters: \ (backslash), / (forward slash),? (question mark), * (asterisk), [ (left square bracket), or ] (right square bracket).
Also read: Rename Sheets Using VBA in Excel
Scenario #9: Create a New Worksheet In the Current Workbook If It Doesn’t Exist
You can prompt the user to enter a name for a new worksheet and only create it with the name provided if it doesn’t already exist in the current workbook. You can use the code below to achieve that:
Sub CreateSheetIfNonExistent()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim SheetName As String
Set Wb = ThisWorkbook
SheetName = InputBox("Enter name for new worksheet:")
If SheetName = "" Then Exit Sub
On Error Resume Next
Set Ws = Wb.Worksheets(SheetName)
On Error GoTo 0
If Ws Is Nothing Then
Wb.Sheets.Add(After:=Wb.Sheets(Wb.Sheets.Count)).Name = SheetName
Else
MsgBox "A worksheet '" & SheetName & "' already exists!", vbExclamation
End If
End SubThe above VBA code prompts the user to input a name for the new worksheet using an input box.
If the user cancels the input box, the subroutine exits. If the user enters a name, the code checks if a worksheet with the inputted name already exists.
The code adds a new worksheet with the specified name if it doesn’t exist. If it does exist, the code displays a message box notifying the user that a worksheet with the same name already exists.
Scenario #10: Create a New Sheet in the Current Workbook Based On a Template
If you need to create a new worksheet in the current workbook based on an existing worksheet as a template, use the following code:
Sub CreateSheetBasedOnTemplate()
Dim Wb As Workbook
Dim Ws As Worksheet
Set Wb = ThisWorkbook
On Error Resume Next
Set Ws = Wb.Worksheets("Template")
On Error GoTo 0
If Ws Is Nothing Then
MsgBox "Template worksheet not found!", vbExclamation
Exit Sub
End If
Ws.Copy After:=Wb.Sheets(Wb.Sheets.Count)
Wb.Worksheets(Wb.Sheets.Count).Name = "NewWorksheet"
End SubThe above VBA code creates a new worksheet in the current workbook based on an existing template worksheet named ‘Template.’
It checks if the template worksheet exists, and if not, it displays a message box and exits the subroutine.
If the template exists, it copies it and renames the copy to ‘NewWorksheet.’
Scenario #11: Create a New Worksheet in Another Open Workbook
The VBA code below creates a new worksheet in another open workbook named ‘Sales.xlsm’ and renames it to ‘NewWorksheet.’
Sub CreateSheetInAnotherOpenWorkbook()
Dim targetWbName As String
Dim targetWb As Workbook
Dim newWs As Worksheet
targetWbName = "Sales.xlsm"
Set targetWb = Workbooks(targetWbName)
Set newWs = targetWb.Sheets.Add
newWs.Name = "NewWorksheet"
End SubScenario #12: Add A Chart Sheet to the Current Workbook
By default, the ‘Sheets.Add’ method adds a worksheet. However, to add a chart sheet dedicated to a single chart, you must define the ‘Type’ parameter with the ‘xlChart’ constant.
The code below adds a chart sheet to the current workbook:
Sub AddChartSheet()
Sheets.Add Type:=xlChart
End SubIn this tutorial, I showed you several scenarios where you can use VBA’s ‘Sheets.Add’ method to add new sheets to a workbook. I hope you found the tutorial helpful.
Other articles you may also like:
