By yse


2011-07-14 03:23:52 8 Comments

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 

21 comments

@Guest 2019-04-04 15:39:43

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

@Tim Williams 2011-07-14 04:27:13

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

@Wudang 2011-10-18 08:37:59

Entirely approriate use IMO. It's a trap for a thing that is posited as existing and doesn't and has a long history - cf perl strict, STAE etc. Upvoted

@sancho.s 2014-09-06 18:49:40

One should probably use ActiveWorkbook instead of ThisWorkbook. The latter refers to the workbook that contains the macro code, which might be different from the workbook than one wants to test. I guess ActiveWorkbook would be useful for most cases (contrived situations are always available, though).

@hornetbzz 2014-11-18 21:25:25

Noob at vba but can't find why this function drives the code exec to error 9. Found the answer here : stackoverflow.com/a/11459834/461212

@findwindow 2015-10-15 20:48:31

I don't get SheetExists = Not sht Is Nothing XD What does that mean? The function is returning the opposite of sheet is nothing??

@Tim Williams 2015-10-15 21:12:01

sht Is Nothing will be True if there's no sheet with that name, but we want to return True if there is a sheet with that name, hence the Not. It's a little easier (but not valid) if you re-arrange a bit to SheetExists = sht Is Not Nothing

@findwindow 2015-10-15 21:30:47

<3 @TimWilliams That makes perfect sense. Now I just need to figure out why I can't get it to work XD

@HK_CH 2015-12-31 08:44:32

Good to note that if you run this code in your personal macro workbook, change from If wb Is Nothing Then Set wb = ThisWorkbook to If wb Is Nothing Then Set wb = ActiveWorkbook

@rory.ap 2016-03-15 12:15:39

This is a highly-efficient approach (see my comments about bench marks under Rory's answer below), so who cares what the detractors think. Note (as of now) you have zero down votes.

@Taylor Scott 2018-06-12 18:41:16

In the interest of spreading high speed code, you can make this 85+% faster by changing wb Is Nothing to IsMissing(wb).

@Tim Williams 2018-06-12 20:32:49

True that's slightly faster but only noticeable if you need to check >10k or 100k sheets...

@Pᴇʜ 2018-11-07 09:46:03

@TimWilliams You should either change to Dim sht As Object or name the function WorksheetExists(…) and use Set sht = wb.Workheets(shtName) otherwise it returns False on checking for eg. chart sheets that actually exist. Eg. SheetExists("Chart1") returns False even if a chart sheet named Chart1 exists. • Sheets != Worksheets

@Tim Williams 2018-11-07 18:03:57

@Pᴇʜ - that is a good point: made an adjustment.

@imjordy23 2018-06-14 15:13:49

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.

@ashleedawg 2018-05-11 16:56:56

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:

@AOBR 2018-04-16 19:24:47

I wrote this one:

Function sheetExist(sSheet As String) As Boolean
On Error Resume Next
sheetExist = (ActiveWorkbook.Sheets(sSheet).Index > 0)
End Function

@ChrisB 2018-06-07 22:45:45

Great function! Not only is it fast, it's also the most concise.

@Juan Joya 2018-12-03 14:59:15

I beleive this is the answer that corresponds to the question the most

@chenaou 2014-03-07 14:47:03

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

@ChrisB 2018-07-26 07:01:44

The problem with this answer is that upon determining that the sheet did in fact exist, it is deleted and therefore no longer exists. If this were written as a function it might have a name like SheetExistsAfterDeletion and would always return FALSE.

@MAx Segura 2016-06-28 02:24:03

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.

@gth826a 2017-07-10 16:54:45

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

@Vityata 2017-05-23 09:25:16

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

@Shrikant 2017-03-28 12:24:50

    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

@Shai Alon 2015-02-17 14:07:54

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

@X37V 2016-08-01 16:37:47

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.

@VirtualMichael 2016-01-07 06:33:32

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.

@Peter Albert 2013-01-24 20:11:20

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") ...

@citizenkong 2014-08-04 09:59:36

This doesn't catch primitive types in Collections as an error will be raised by the Set keyword. I found that rather than using Set, asking for the TypeName of the member of the collection works for all cases, i.e. TypeName objCollection(strName)

@jeffreyweir 2015-05-28 22:56:44

@Peter: Best to add something to clear the error that will get raised in the case of non existance before the funciton terminates - either an err.clear or On Error Resume Next. Otherwise the error handling in the calling procedure could be inadvertantly triggerred in cases like the following. Sub Test() On Error GoTo errhandler Debug.Print Contains(Workbooks, "SomeBookThatIsNotOpen") errhandler: If Err.Number <> 0 Then Stop End Sub

@Shai Alon 2015-02-17 15:22:04

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

@Rory 2015-02-12 09:25:59

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

@Siddharth Rout 2015-08-07 03:18:15

++ Another good one!

@findwindow 2015-10-15 20:59:31

Don't you have to specify the workbook?

@bp_ 2016-02-08 13:56:54

One simple evaluation. It doesn't even need a separate function IMO. Well played!

@Cornel 2016-03-03 19:50:11

Really simple and efficient code and a ton of thanks for helping me discover the Evaluate command. Respect!

@rory.ap 2016-03-15 12:10:34

@Rory I ran some benchmark tests on this vs. Tim Williams' answer. Over 500,000 loops, yours took 22 seconds and Tim's took <1.

@rory.ap 2016-03-15 12:11:10

@Cornel -- See my comment. Be careful when you bandy around the word "efficient".

@rory.ap 2016-03-15 12:11:46

@Rory -- Nice name though. And I still upvoted.

@Rory 2016-03-15 14:13:57

@roryap - if you need to run this 500,000 times, you need to rethink your entire approach. ;)

@rory.ap 2016-03-15 14:15:26

@Rory -- totally agree. I just thought it was interesting and something for posterity.

@tedcurrent 2016-03-16 14:41:26

@roryap - however, using several slow methods will start piling up seconds. I would say this is extremely valuable information, as Excel "applications" start to rack up seconds pretty easily with various Range methods etc.

@rory.ap 2016-03-16 14:49:14

@tedcurrent -- So with whom are you in agreement? I'm not sure I'm clear...

@tedcurrent 2016-03-16 15:18:32

@roryap - that information is valuable to the conversation in what way? I'm simply stating that scattering inefficient methods around your code will make the application slow as a whole. you testing this 500k times is awesome and I thank you for doing it, 22 seconds is not great. (I agree with you)

@Sascha L. 2017-09-08 09:46:41

Even if it's slower, it looks like a much cleaner solution than the accepted answer. +1 from me.

@ScottMcC 2015-01-16 07:55:06

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

@uildriks 2014-08-03 15:43:43

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

.

@Martin Carlsson 2014-01-09 09:26:05

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

@Philipp88 2013-08-05 10:56:52

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

@fbonetti 2013-03-27 20:21:55

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

Related Questions

Sponsored Content

17 Answered Questions

[SOLVED] How do I tell if a regular file does not exist in Bash?

  • 2009-03-12 14:48:43
  • Bill the Lizard
  • 2307422 View
  • 2920 Score
  • 17 Answer
  • Tags:   bash file-io scripting

1 Answered Questions

Copy range of cells from multiple sheets into one

  • 2019-01-31 15:05:16
  • Chris
  • 22 View
  • -1 Score
  • 1 Answer
  • Tags:   excel vba

1 Answered Questions

Copying sheets to existing workbook, workbook name is cell.value

  • 2019-01-30 20:33:50
  • Daisy
  • 49 View
  • 1 Score
  • 1 Answer
  • Tags:   excel vba

14 Answered Questions

[SOLVED] How to mkdir only if a dir does not already exist?

3 Answered Questions

[SOLVED] loop through specified sheets in VBA

3 Answered Questions

[SOLVED] I am looking to combine multiple sheets into a single consolidated sheet

0 Answered Questions

Loop through excel spreadsheets in different workbooks

  • 2017-04-19 16:54:49
  • Ashley
  • 62 View
  • 0 Score
  • 0 Answer
  • Tags:   excel vba loops

3 Answered Questions

[SOLVED] Add worksheet name to rows in multiple sheets

  • 2016-06-21 12:53:40
  • Polyvios Moukoulis
  • 120 View
  • 0 Score
  • 3 Answer
  • Tags:   excel-vba vba excel

2 Answered Questions

[SOLVED] sheet array subscript out of range

1 Answered Questions

[SOLVED] Excel Macro: iterate through workbooks and print individual sheets from each

  • 2012-01-26 16:28:09
  • RocketGoal
  • 4274 View
  • 1 Score
  • 1 Answer
  • Tags:   excel vba iteration

Sponsored Content