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
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
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.
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 wrote this one:
Function sheetExist(sSheet As String) As Boolean
On Error Resume Next
sheetExist = (ActiveWorkbook.Sheets(sSheet).Index > 0)
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
Short and clean:
Function IsSheet(n$) As Boolean
IsSheet = Not IsError(Evaluate(n & "!a1"))
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
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
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:
=ISREF(INDIRECT("SheetName!A1"))
This will return TRUE if the workbook contains a sheet called SheetName and FALSE otherwise.
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
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
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"
For Each…Next Statement (VBA)Exit Statement (VBA) 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
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
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
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.
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
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
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") ...
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
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.
Source: Stackoverflow.com