Tested in Excel 365 (16.8730.2046) 64-bit
- Public Function SheetExists(SheetToFind As String) As Boolean
- On Error Resume Next
- Sheets(SheetToFind).Activate
- If Err.Number = 0 Then
- SheetExists = True
- Else
- Err.Number = 0
- End If
- Sheets(sheet).Activate
- On Error GoTo 0
- End Function
Or checking each sheet name. I personally prefer this method because I like to avoid as much as possible the number of On Error Resume Next and On Error GoTo 0. Also, using Like allows to search using an approximate name.
- Public Function SheetExists(SheetName) As Boolean
- Dim i, test
- test = False
- For i = 1 To Sheets.Count
- If UCase(Sheets(i).Name) Like UCase(SheetName) Then
- test = True
- Exit For
- End If
- Next i
- SheetExists = test
- End Function
Or return the exact name using Like to search an approximate name.
- Public Function GetSheetExactName(SheetName) As String
- Dim i, test
- test = ""
- For i = 1 To Sheets.Count
- If UCase(Sheets(i).Name) Like UCase(SheetName) Then
- test = Sheets(i).Name
- Exit For
- End If
- Next i
- GetSheetExactName = test
- End Function