Here’s an example.
Option Explicit Sub executeMacro() On Error GoTo ErrHandler Dim sWS As String sWS = "Sheet2" ' Worksheet to be checked. If Not chkWorkSheetExists(sWS) Then ' This is optional. Create and add the worksheet if not found. ThisWorkbook.Sheets.Add _ (After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)).Name = sWS End If ErrHandler: ' End Sub ' Check if worksheets exists. Function chkWorkSheetExists(sSheetName As String) As Boolean On Error Resume Next Dim sSht As Worksheet Set sSht = ThisWorkbook.Sheets(sSheetName) chkWorkSheetExists = Not sSht Is Nothing ' Return true if worksheets exists. End Function
In the above example, I have hardcoded the worksheet name (Sheet2) that I want to check in my current workbook. The property ThisWorkBook returns the current workbook object.
User Defined Function chkWorkSheetExists()
The UDF (or the user defined function) chkWorkSheetExists() returns a Boolean true or false based on the result. The function takes a parameter that is the name of the worksheet. And it checks if the given worksheet exists in the current workbook.
There may be other ways to do this in VBA. However, this method also works and its simple.
Programmatically Create and Add a Worksheet if it Does not Exist
It is optional. In-addition, I have added a code to create the sheet if it does not exists. Even though, it is optional, I thought I’ll share this little piece of macro here as would be useful for some.
' Add a sheet "after" the last available sheet. ThisWorkbook.Sheets.Add _ (After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)).Name = sWS
You can further simplify the above one-liner using the with clause like this.
With ThisWorkbook .Sheets.Add(After:=.Worksheets(.Worksheets.Count)).Name = sWS End With
Check if Worksheet Exists in another Workbook
In the first example above, I was checking if a particular worksheet exists in the current workbook. Now, using the same method, I’ll check if a particular worksheet exists in another workbook.
The method is simple, except that I have to provide the workbook (or file) path and the name of the worksheet.
Option Explicit Dim objSrc As Workbook Sub executeMacro() On Error GoTo ErrHandler Application.ScreenUpdating = False Dim sWS As String sWS = "Year - 2021" ' Worksheet to be checked. If Not chkWorkSheetExists(sWS, "d:\\Books.xlsx") Then Debug.Print "Worksheet does not exist" Else Debug.Print "Worksheet exist" End If ErrHandler: Application.ScreenUpdating = True End Sub ' Check if worksheets exists. Function chkWorkSheetExists(sSheetName As String, sFilePath As String) As Boolean On Error Resume Next Set objSrc = Workbooks.Open(sFilePath, True, True) ' Open the file. Dim sSht As Worksheet Set sSht = objSrc.Worksheets(sSheetName) ' Check if the worksheet exists. ' Close the source file. objSrc.Close saveChanges:=False ' Close the workbook without saving changes. Set objSrc = Nothing chkWorkSheetExists = Not sSht Is Nothing End Function
In this example, the user defined function chkWorkSheetExists() takes two parameters, the name of the worksheet and workbook path. It will return a Boolean value (true or false). The source workbook is defined in the beginning of the macro (Dim objSrc As Workbook).
Thanks for reading. ☺