Test or check if sheet exists
Dim wkbkdestination As Workbook Dim destsheet As Worksheet For Each ThisWorkSheet In wkbkorigin.Worksheets 'this throws subscript out of range if there is not a sheet in the destination 'workbook that has the same name as the current sheet in the origin workbook. Set destsheet = wkbkdestination.Worksheets(ThisWorkSheet.Name) Next
Basically I loop through all sheets in the origin workbook then set destsheet in the destination workbook to the sheet with the same name as the currently iterated one in the origin workbook.
How can I test if that sheet exists? Something like:
If wkbkdestination.Worksheets(ThisWorkSheet.Name) Then
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 Nothing End Function
If you are specifically interested in worksheets only, you can use a simple Evaluate call:
Function WorksheetExists(sName As String) As Boolean WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)") End 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:
For i = 1 To Worksheets.Count If Worksheets(i).Name = "MySheet" Then exists = True End If Next i If Not exists Then Worksheets.Add.Name = "MySheet" End If
As checking for members of a collection is a general problem, here is an abstracted version of Tim's answer:
Function Contains(objCollection As Object, strName as String) As Boolean Dim o as Object On Error Resume Next set o = objCollection(strName) Contains = (Err.Number = 0) Err.Clear End Function
This function can be used with any collection like object (Shapes, Range, Names, Workbooks, etc.).
To check for the existence of a sheet, use If Contains(Sheets, "SheetName") ...
Corrected: Without error-handling:
Function CheckIfSheetExists(SheetName As String) As Boolean CheckIfSheetExists = False For Each WS In Worksheets If SheetName = WS.name Then CheckIfSheetExists = True Exit Function End If Next WS End Function
In case anyone wants to avoid VBA and test if a worksheet exists purely within a cell formula, it is possible using the ISREF and INDIRECT functions:
This will return TRUE if the workbook contains a sheet called SheetName and FALSE otherwise.
My solution looks much like Tims but also works in case of non-worksheet sheets - charts
Public Function SheetExists(strSheetName As String, Optional wbWorkbook As Workbook) As Boolean If wbWorkbook Is Nothing Then Set wbWorkbook = ActiveWorkbook 'or ThisWorkbook - whichever appropriate Dim obj As Object On Error GoTo HandleError Set obj = wbWorkbook.Sheets(strSheetName) SheetExists = True Exit Function HandleError: SheetExists = False End Function
I wrote this one:
Function sheetExist(sSheet As String) As Boolean On Error Resume Next sheetExist = (ActiveWorkbook.Sheets(sSheet).Index > 0) End Function
Put the test in a function and you will be able to reuse it and you have better code readability.
Do NOT use the "On Error Resume Next" since it may conflict with other part of your code.
Sub DoesTheSheetExists() If SheetExist("SheetName") Then Debug.Print "The Sheet Exists" Else Debug.Print "The Sheet Does NOT Exists" End If End Sub Function SheetExist(strSheetName As String) As Boolean Dim i As Integer For i = 1 To Worksheets.Count If Worksheets(i).Name = strSheetName Then SheetExist = True Exit Function End If Next i End Function
Why not just use a small loop to determine whether the named worksheet exists? Say if you were looking for a Worksheet named "Sheet1" in the currently opened workbook.
Dim wb as Workbook Dim ws as Worksheet Set wb = ActiveWorkbook For Each ws in wb.Worksheets if ws.Name = "Sheet1" then 'Do something here End if Next
Many years late, but I just needed to do this and didn't like any of the solutions posted... So I made one up, all thanks to the magic of (SpongeBob rainbow hands gesture) "Evaluate()"!
Evaluate("IsError(" & vSheetName & "!1:1)")
Returns TRUE if Sheet does NOT exist; FALSE if sheet DOES exist. You can substitute whatever range you like for "1:1", but I advise against using a single cell, cuz if it contains an error (eg, #N/A), it will return True.
If you are a fan of WorksheetFunction. or you work from a non-English country with a non-English Excel this is a good solution, that works:
WorksheetFunction.IsErr(Evaluate("'" & wsName & "'!A1"))
Or in a function like this:
Function WorksheetExists(sName As String) As Boolean WorksheetExists = Not WorksheetFunction.IsErr(Evaluate("'" & sName & "'!A1")) End Function
Compact wsExists function (without reliance on Error Handling!)
Here's a short & simple function that doesn't rely on error handling to determine whether a worksheet exists (and is properly declared to work in any situation!)
Function wsExists(wsName As String) As Boolean Dim ws: For Each ws In Sheets wsExists = (wsName = ws.Name): If wsExists Then Exit Function Next ws End Function
The following example adds a new worksheet named myNewSheet, if it doesn't already exist:
If Not wsExists("myNewSheet") Then Sheets.Add.Name = "myNewSheet"
Public Function WorkSheetExists(ByVal strName As String) As Boolean On Error Resume Next WorkSheetExists = Not Worksheets(strName) Is Nothing End Function sub test_sheet() If Not WorkSheetExists("SheetName") Then MsgBox "Not available" Else MsgBox "Available" End If End Sub
I did another thing: delete a sheet only if it's exists - not to get an error if it doesn't:
Excel.DisplayAlerts = False Dim WS For Each WS In Excel.Worksheets If WS.name = "Sheet2" Then Excel.sheets("Sheet2").Delete Exit For End If Next Excel.DisplayAlerts = True
For Each Sheet In Worksheets If UCase(Sheet.Name) = "TEMP" Then 'Your Code when the match is True Application.DisplayAlerts = False Sheet.Delete Application.DisplayAlerts = True '----------------------------------- End If Next Sheet
Change "Data" to whatever sheet name you're testing for...
On Error Resume Next Set DataSheet = Sheets("Data") If DataSheet Is Nothing Then Sheets.Add(after:=ActiveSheet).Name = "Data" ''or whatever alternate code you want to execute'' End If On Error GoTo 0
Without any doubt that the above function can work, I just ended up with the following code which works pretty well:
Sub Sheet_exist () On Error Resume Next If Sheets("" & Range("Sheet_Name") & "") Is Nothing Then MsgBox "doesnt exist" Else MsgBox "exist" End if End sub
Note: Sheets_Name is where I ask the user to input the name, so this might not be the same for you.
I came up with an easy way to do it, but I didn't create a new sub for it. Instead, I just "ran a check" within the sub I was working on. Assuming the sheet name we're looking for is "Sheet_Exist" and we just want to activate it if found:
Dim SheetCounter As Integer SheetCounter = 1 Do Until Sheets(SheetCounter).Name = "Sheet_Exist" Or SheetCounter = Sheets.Count + 1 SheetCounter = SheetCounter +1 Loop If SheetCounter < Sheets.Count + 1 Then Sheets("Sheet_Exist").Activate Else MsgBox("Worksheet ""Sheet_Exist"" was NOT found") End If
I also added a pop-up for when the sheet doesn't exist.
I know it is an old post, but here is another simple solution that is fast.
Public Function worksheetExists(ByVal wb As Workbook, ByVal sheetNameStr As String) As Boolean On Error Resume Next worksheetExists = (wb.Worksheets(sheetNameStr).Name <> "") Err.Clear: On Error GoTo 0 End Function
I actually had a simple way to check if the sheet exists and then execute some instruction:
In my case I wanted to delete the sheet and then recreated the same sheet with the same name but the code was interrupted if the program was not able to delete the sheet as it was already deleted
Sub Foo () Application.DisplayAlerts = False On Error GoTo instructions Sheets("NAME OF THE SHEET").Delete instructions: Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = "NAME OF THE SHEET" End Sub