Excel 2010 Macro Check If Sheet in Workbook Exists
This function requires that the workbook(s) it checks be open. It returns True if the sheet is found and False if it is not:
SheetExists(SName, WBName)
The arguments are:
- SName - The name of the sheet being searched
- WBName - (Optional) The name of the workbook containing the sheet
Function SheetExists(SName As String, Optional WB As Workbook) As Boolean
Dim WS As Worksheet
‘ Use active workbook by default
If WB Is Nothing Then
Set WB = ActiveWorkbook
End If
On Error Resume Next
SheetExists = CBool(Not WB.Sheets(SName) Is Nothing)
On Error GoTo 0
End Function
An example of using this function:
Sub CheckForSheet()
Dim ShtExists As Boolean
ShtExists = SheetExists(“Sheet9”)
‘notice that only one parameter was passed; the workbook name is optional
If ShtExists Then
MsgBox “The worksheet exists!”
Else
MsgBox “The worksheet does NOT exist!”
End If
End Sub