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
asked Jul 14, 2011 at 3:23
1
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
darcyy
5,1565 gold badges27 silver badges40 bronze badges
answered Jul 14, 2011 at 4:27
Tim WilliamsTim Williams
142k8 gold badges91 silver badges118 bronze badges
11
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
answered Feb 12, 2015 at 9:25
RoryRory
31.4k5 gold badges30 silver badges33 bronze badges
10
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 Boolean
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
answered Mar 27, 2013 at 20:21
fbonettifbonetti
6,4643 gold badges33 silver badges32 bronze badges
1
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"] ...
shA.t
16.1k5 gold badges50 silver badges107 bronze badges
answered Jan 24, 2013 at 20:11
Peter AlbertPeter Albert
16.6k4 gold badges63 silver badges88 bronze badges
2
I wrote this one:
Function sheetExist[sSheet As String] As Boolean
On Error Resume Next
sheetExist = [ActiveWorkbook.Sheets[sSheet].Index > 0]
End Function
answered Apr 16, 2018 at 19:24
AOBRAOBR
2492 silver badges4 bronze badges
3
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
answered Feb 17, 2015 at 14:07
Shai AlonShai Alon
93513 silver badges20 bronze badges
0
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.
answered Jan 7, 2016 at 6:33
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
Example Usage:
The following example adds a new worksheet named myNewSheet
, if it doesn't already exist:
If Not wsExists["myNewSheet"] Then Sheets.Add.Name = "myNewSheet"
More Information:
- MSDN :
For Each
…Next
Statement [VBA] - MSDN :
Exit
Statement [VBA] - MSDN : Comparison Operators [VBA]
answered May 11, 2018 at 16:56
ashleedawgashleedawg
19.4k7 gold badges69 silver badges100 bronze badges
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
.
answered Aug 3, 2014 at 15:43
uildriksuildriks
511 silver badge1 bronze badge
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.
answered Aug 1, 2016 at 16:37
1
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
answered Jan 9, 2014 at 9:26
Martin CarlssonMartin Carlsson
4412 gold badges6 silver badges16 bronze badges
Short and clean:
Function IsSheet[n$] As Boolean
IsSheet = Not IsError[Evaluate["'" & n & "'!a1"]]
End Function
answered Apr 3, 2020 at 4:13
Excel HeroExcel Hero
13.9k4 gold badges28 silver badges38 bronze badges
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
answered Aug 5, 2013 at 10:56
M1NTM1NT
3761 gold badge4 silver badges12 bronze badges
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
answered Jan 16, 2015 at 7:55
ScottMcCScottMcC
3,7611 gold badge26 silver badges34 bronze badges
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
answered Mar 28, 2017 at 12:24
ShrikantShrikant
5134 silver badges15 bronze badges
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
answered May 23, 2017 at 9:25
VityataVityata
41.7k7 gold badges52 silver badges89 bronze badges
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
Dan Lowe
47.1k17 gold badges118 silver badges110 bronze badges
answered Jul 10, 2017 at 16:54
gth826agth826a
911 silver badge4 bronze badges
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.
Cody Gray♦
234k50 gold badges482 silver badges559 bronze badges
answered Jun 28, 2016 at 2:24
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
answered Feb 17, 2015 at 15:22
Shai AlonShai Alon
93513 silver badges20 bronze badges
I use this function to check and return a new sheet name if needed. WSname is the desired worksheet name and WBCur is the workbook you would like to check in. I use this because there is no need for error handling and can call it whenever i am creating a new worksheet.
Public Function CheckNewWorksheetName[WSName As String, WBCur As Workbook] 'Will return New Name if needed
Dim NewWSNum As Long, A As Integer, B As Integer, WorksheetFound As Boolean
NewWSNum = 1
WorksheetFound = False
For A = 1 To WBCur.Worksheets.Count
If WBCur.Worksheets[A].Name = WSName Then
A = WBCur.Worksheets.Count
WorksheetFound = True
End If
Next A
If WorksheetFound = False Then
CheckNewWorksheetName = WSName
Else
Do While WorksheetFound = True
WorksheetFound = False
For B = 1 To WBCur.Worksheets.Count
If WBCur.Worksheets[B].Name = WSName & "_" & NewWSNum Then
B = WBCur.Worksheets.Count
WorksheetFound = True
NewWSNum = NewWSNum + 1
End If
Next B
Loop
CheckNewWorksheetName = WSName & "_" & NewWSNum
End If
End Function
answered Jul 14, 2021 at 19:51
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.
answered Jun 14, 2018 at 15:13
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
answered Apr 4, 2019 at 15:39
GuestGuest
4302 silver badges4 bronze badges
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
Cody Gray♦
234k50 gold badges482 silver badges559 bronze badges
answered Mar 7, 2014 at 14:47
1