Excel VBA: Check If a Sheet Exists (2 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

One of the many things that we can accomplish using VBA in Excel is that we can check if a particular sheet exists in a workbook or not. In this article, I’ll show you how you can check if a single sheet or a number of sheets exist using Excel VBA.


Excel VBA: Check If a Sheet Exists (Quick View)

Sub Check_If_a_Sheet_Exists()

Workbook_Name = "Check If a Sheet Exists.xlsm"
Sheet_Name = "Sheet1"

Count = 0
For i = 1 To Workbooks(Workbook_Name).Sheets.Count
    If Sheets(i).Name = Sheet_Name Then
        Count = Count + 1
        Exit For
    End If
Next i

If Count > 0 Then
    MsgBox "The Sheet Exists."
Else
    MsgBox "The Sheet doesn't Exist."
End If

End Sub

VBA Code to Check If a Sheet Exits in Excel


Developing Macros to Check If a Sheet Exists or Not Using Excel VBA

Without further delay, let’s move to our main discussion today. Our objective is to develop an Excel Macro to check whether a particular sheet exists or not using Excel VBA. We’ll divide the session into two groups. First, we’ll check for a single worksheet, then for a number of worksheets.


1. Developing a Macro to Check If a Single Sheet Exists or Not

First, we’ll learn to develop a Macro to check whether a single sheet exists or not. For example, let’s check whether Sheet1 exists or not in this workbook (Check If a Sheet Exists.xlsx).

⧪ Step 1: Opening the Visual Basic Window

Press ALT + F8 to open the Visual Basic window.

Opening VBA Window to Check If a Sheet Exists or Not Using Excel VBA

⧪ Step 2: Inserting a New Module

Go to the Insert > Module option in the toolbar. Click on Module. A new Module called Module1 will open.

Inserting New Module to Check If a Sheet Exists or Not Using Excel VBA

⧪ Step 3: Putting the VBA Code

This is the time for putting the VBA code. It is already available at the top of the article. Here I’m providing a step-by-step breakdown of the code for easy understanding.

➽ Section 1: Inserting the Necessary Inputs

First of all, we have to insert the necessary inputs into the code. These include the name of the workbook (Check If a Sheet Exists.xlsx) and the worksheet (Sheet1).

Workbook_Name = "Check If a Sheet Exists.xlsm"
Sheet_Name = "Sheet1"

➽ Section 2: Iterating through All the Sheets to Check Whether the Given Name Matches a Sheet or Not
Next, we’ll iterate through a for-loop to check whether the name of the sheet matches any sheet of the workbook or not. We’ll use a variable called Count for this purpose. If any sheet does match, the value of Count will increment from 0 to 1.

Count = 0
For i = 1 To Workbooks(Workbook_Name).Sheets.Count
    If Sheets(i).Name = Sheet_Name Then
        Count = Count + 1
        Exit For
    End If
Next i

➽ Section 3: Showing the Output

Finally, it’s the time for the output. If the value of Count is more than 1, it means the Sheet exists. And if it’s equal to 0, it means the sheet doesn’t exist.

If Count > 0 Then
    MsgBox "The Sheet Exists."
Else
    MsgBox "The Sheet doesn't Exist."
End If

Therefore, the complete VBA code will be:

⧭ VBA Code:

Sub Check_If_a_Sheet_Exists()

Workbook_Name = "Check If a Sheet Exists.xlsm"
Sheet_Name = "Sheet1"

Count = 0
For i = 1 To Workbooks(Workbook_Name).Sheets.Count
    If Sheets(i).Name = Sheet_Name Then
        Count = Count + 1
        Exit For
    End If
Next i

If Count > 0 Then
    MsgBox "The Sheet Exists."
Else
    MsgBox "The Sheet doesn't Exist."
End If

End Sub

VBA Code to Check If a Sheet Exits in Excel

⧪ Step 4: Running the Code

Now the final step, that’s to run the code. For running the code, again go to the Visual Basic toolbar and click on the button Run Sub \ Userform.

As sheet Sheet1 exists on the workbook, a message box will display “The Sheet Exists.”.

Output to Check If a Sheet Exists or Not Using Excel VBA

If it didn’t exist, the message box would display “The Sheet doesn’t Exist.”.


2. Embedding a Macro to Check If Multiple Sheets Exist or Not

We’ve developed a Macro to check for a single sheet. Now we’ll try to develop it for multiple sheets.

For example, let’s check if Sheet1, Sheet2, and Sheet4 exist in the workbook “Check If a Sheet Exists.xlsm” or not.

The steps are all same as the steps mentioned in section 1, except for the VBA code.

Now, can you guess the code?

You can try of your own to develop and run one. In case you can’t, I am providing my code here. Of course, you can change and modify it on your own.

⧭ VBA Code:

Sub Check_If_a_Sheet_Exists()

Workbook_Name = "Check If a Sheet Exists.xlsm"
Sheet_Names = Array("Sheet1", "Sheet2", "Sheet3")

For i = LBound(Sheet_Names) To UBound(Sheet_Names)

    Count = 0
    For j = 1 To Workbooks(Workbook_Name).Sheets.Count
        If Sheets(j).Name = Sheet_Names(i) Then
            Count = Count + 1
        Exit For
        End If
    Next j

    If Count > 0 Then
        Output = Output + Sheet_Names(i) + " Exists." + vbNewLine + vbNewLine
    Else
        Output = Output + Sheet_Names(i) + " doesn't Exist." + vbNewLine + vbNewLine
    End If
Next i

MsgBox Output

End Sub

⧭ Output:

Run this code. It’ll display Sheet1 and Sheet2 exists, and Sheet3 doesn’t exist.

Read More: Excel VBA: Check If a File Exists or Not


Things to Remember

We’ve specified the names of the workbooks within which we are searching for the worksheets. But if you want to search within the active workbook only, you can use the ActiveWorkbook or ThisWorkbook property of VBA.

In fact, if you don’t mention the name of any workbook, directly mention the names of the worksheets, that’ll also do. Excel will automatically work upon the active workbook.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

So, these are the ways to check if a sheet exists or not using VBA in Excel. Do you have any questions? Feel free to ask us.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. As I use mainly VBScript to work on various Excel Reports I have developped a small function to check the existence of a sheet :

    Function FeuilleExiste(ByRef pobjWB, ByVal pstrFeuille)
    On Error Resume Next
    pobjWB.Worksheets(pstrFeuille).Select
    If err.number 0 Then
    FeuilleExiste = False
    Else
    FeuilleExiste = True
    End If
    On Error GoTo 0
    End Function

    I am just using the fact that the select function generates an error if the sheet does not exist and I trtap this error.
    For me it is the fastest method as I create Excel files with hundreds of sheets.

    Hope this helps.

    • Reply Avatar photo
      Shahriar Abrar Rafid Jul 9, 2023 at 12:04 PM

      Hello JEFF,

      Thank you so much for your valuable contribution to the discussion on our blog. We appreciate you taking the time to share the first code snippet for checking the existence of a worksheet. It’s always great to see different approaches and perspectives being shared, and your code provides an alternative solution to the problem.

      If you have a small number of sheets or prefer a more explicit and controlled check, then our code with the loop can still be a valid option. And your approach avoids unnecessary iteration through all sheets and provides a straightforward way to check the existence of a worksheet.

      Once again, thank you for your participation and we value the engagement of our readers, and your comment adds even more depth to the topic.

      Hope to see more of your valuable contributions in the future!

      Regards,
      SHAHRIAR ABRAR RAFID
      Team ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo