Test or check if sheet exists
Some folk dislike this approach because of an "inappropriate" use of error handling, but I think it's considered acceptable in VBA... An alternative approach is to loop though all the sheets until you find a match.
Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean Dim sht As Worksheet If wb Is Nothing Then Set wb = ThisWorkbook On Error Resume Next Set sht = wb.Sheets(shtName) On Error GoTo 0 WorksheetExists = Not sht Is NothingEnd Function
You don't need error handling in order to accomplish this. All you have to do is iterate over all of the Worksheets and check if the specified name exists:
Dim exists As BooleanFor i = 1 To Worksheets.Count If Worksheets(i).Name = "MySheet" Then exists = True End IfNext iIf Not exists Then Worksheets.Add.Name = "MySheet"End If