Excel VBA Worksheets.Add Method to add new worksheet

This tutorial explains how to add new work sheets using Worksheets.Add Method in Excel VBA, or add new worksheets at the back or before specific worksheet

Excel VBA Worksheets.Add Method to add new worksheet

Excel VBA Worksheets.Add Method is to add new worksheet in a workbook.

Syntax of Excel VBA Worksheets.Add Method

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

Before	Optional. Add new worksheet before specific worksheet
After	Optional, add new worksheet after specific worksheet
Count	Optional. Number of worksheets to add, default is 1
Type	Optional. Worksheet type, default is xlWorksheet xlWorksheet xlChart xlExcel4MacroSheet xlExcel4IntlMacroSheet

If Before and After are not specified, worksheet is added before Active worksheet (the worksheet you selected before running the Add Method).

Example 1 – Add new worksheet after specific worksheet

The below code add new worksheet after Sheet8

Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets("Sheet8"))

Example 2 – Add new worksheet and move to the end of worksheet

The below code makes use of Worksheets(Worksheets.Count) to find the last worksheet. Worksheets(Index) returns the Nth worksheet from left to right.

Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))

Example 3 – add new worksheet if not exist

The below code makes use of a custom Function called wsExist to check if a worksheet already exists, returns TRUE if exists, FALSE if not exist.

Sub createWS()
    Dim ws, newWS As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If Not wsExists("worksheet1") Then
            Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets("Sheet8"))
            newWS.Name = "worksheet1"
        End If
    Next ws
End Sub
Function wsExists(wksName As String) As Boolean
    On Error Resume Next
    wsExists = CBool(Len(Worksheets(wksName).Name) > 0)
    On Error GoTo 0
End Function

Outbound References

https://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.excel.worksheets.add.aspx?f=255&MSPPError=-2147217396

Excel VBA Worksheets.Add Method to add new worksheet