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

You may also want to read:

Excel VBA Worksheets.Copy Method to copy 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 and move to the first worksheet

Set newWS = ThisWorkbook.Worksheets.Add(Before:=Worksheets(1))

Example 4 – 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

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *