check if worksheet exists

VBA to Check If Sheet Exists

Before you perform operations such as moving, copying, or deleting a worksheet in Excel VBA , it is essential to check whether the target worksheet exists in the given workbook to avoid runtime errors.

If you want to check whether a specific sheet exists in a workbook using VBA, you can do so by looping through all the sheets in the workbook and comparing their names with the sheet you are looking for.

If there is a match, it means that the sheet exists in the workbook. Conversely, the sheet does not exist in the workbook if there is no match.

In this tutorial, I will show you several scenarios where you can apply VBA to check whether a sheet exists in a given workbook.

Table of Contents

Scenario #1: Check Whether a Sheet Exists in the Current Workbook

The following Excel VBA code checks whether the worksheet ‘Electronics’ exists in the current workbook:

When you execute the code, and the worksheet you are checking for exists in the current workbook, the code returns a message box indicating that the sheet exists in the workbook:

If the sheet you are checking for does not exist in the current workbook, the code returns a message box showing that the sheet does not exist in the current workbook:

Explanation of the Code

The VBA code iterates through each worksheet in the current workbook, checking if the sheet’s name matches ‘Electronics.’ 

If the code finds a match, it sets the Boolean variable to true and exits the loop.

Finally, it displays a message box indicating whether the sheet exists or not based on the value of the Boolean variable. Notice that the name of the sheet we are checking is hard-coded in the code.

Scenario #2: Ask the User for a Sheet Name and Check if It Exists

Instead of hard-coding a sheet’s name, you can write VBA code that prompts the user to enter the name to check. 

You can use the VBA code below to prompt the user to enter the name of a worksheet and check whether the sheet exists in the current workbook:

This Excel VBA code is similar to the code in Scenario #1, except that it asks the user to enter the name of a worksheet to check. 

After the user enters the worksheet’s name to check, the code loops through each worksheet in the current workbook, checking if the sheet’s name matches the one provided by the user.  

If the code finds a match, it sets the Boolean variable to true, exits the loop, and displays a message box indicating whether the sheet exists based on the value of the Boolean variable. 

Notice that if the user cancels the input box, the subroutine ends.

Scenario #3: Check Whether a Sheet Exists in Another Open Workbook

You can use the VBA code below to check if a sheet exists in another open workbook:

This VBA code prompts the user to input the name of a worksheet and the name of an open workbook. It then checks if the specified worksheet exists within the target open workbook.

If the worksheet exists, the code displays a message indicating the sheet exists; otherwise, it informs the user that it does not exist in the target workbook.

Note: You can hard-code the sheet to check and the workbook in which to check instead of prompting the user to input them.

Scenario #4: Check Whether a Sheet Exists and Create It If It Doesn’t

The example VBA code below checks whether the sheet ‘Electronics’ exists in the current workbook.

If the sheet exists, the code displays a message box indicating that the sheet exists. If the sheet does not exist, the code creates a new worksheet with the name ‘Electronics’  in the current workbook:

This VBA code asks the user to input the name of a worksheet. It then checks if a worksheet with that name exists within the current workbook.

If the worksheet exists, it displays a message indicating it exits.

If the worksheet does not exist, it creates a new worksheet with the inputted name and notifies the user that a new sheet has been created.

Scenario #5: Open a Closed Workbook and Check Whether it Contains a Specific Sheet

Below is the code that will open a closed workbook and check whether a specific sheet exists in it or not.

This VBA code prompts the user to provide the worksheet’s name and the workbook’s path. It opens the specified workbook in read-only mode without updating links or displaying prompts.

The code then checks whether the specified worksheet exists in the opened workbook.

Once the code completes the check, it closes the workbook and informs the user whether the sheet exists in the target workbook.

In this tutorial, I showed you five scenarios of checking whether a sheet exists in a given workbook. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• What is VBA in Excel?
• Rename Sheets Using VBA in Excel
• Useful Excel VBA Macro Codes Examples
• Check If Workbook Is Open Using VBA

Steve Scott

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

How to Check IF a Sheet Exists using VBA in Excel

- Written by Puneet

If you want to create a sheet, want to delete it, or move or copy it, there’s one thing that you need to know if that sheet exists or not.

To write code to check whether the sheet exists or not you need a loop that loops through each sheet in the workbook and matches the name you have provided. But here’s the thing, you can use two different loops for this ( For Next and For Each ), and today we will use both.

In this tutorial, we will look at different ways to do that, so, make sure to have the developer tab on your ribbon and open the VBA editor to write this code.

Check IF a Sheet Exists in the Current Workbook

With this loop, you can refer to all the sheets in the workbook and loop through each one by one to match the name of the sheet with the sheet name that you want to search for.

Follow these steps:

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Let me explain how this works: When you run this code, it shows you a message where you need to enter the sheet name that you wish to find.

After that, it loops through each sheet to match the name with the name you have entered, and if the name matches with a sheet, it shows you a message and another message if there’s no match.

Here is another code to check if a sheet exists or not.

This code uses the FOR NEXT loop and uses the total count of sheets in the workbook, and based on that, perform a loop in each sheet that matches the name with the name you have entered.

Check IF Sheet Exists in Closed Workbook

In the following code, you have a loop that searches for the sheet name in a closed workbook. To refer to the file, we used the file address.

When you run this macro, it opens the file at the back end as you have turned OFF the screen updating, and once it loops through all the sheets, you have code to turn ON the screen updating.

Note: As you can see, in the file location address, we have the file extension which means you need to have the correct extension of the file to refer to it.

Related Tutorials

• CLEAR an Entire Sheet using VBA in Excel
• Copy and Move a Sheet in Excel using VBA
• COUNT Sheets using VBA in Excel
• DELETE a SHEET using VBA in Excel
• Hide & Unhide a Sheet using VBA in Excel
• PROTECT and UNPROTECT a Sheet using VBA in Excel
• RENAME a Sheet using VBA in Excel
• Write a VBA Code to Create a New Sheet in Excel
• VBA Worksheet Object
• Activate a Sheet using VBA

How To Check If A Worksheet Exists Using VBA

There may come a time when you need to know if a sheet in a workbook exists either during VBA code execution or as a result within the workbook. You may be creating and deleting sheets with your VBA code and will need to test if a sheet exists before it’s created/deleted to avoid run-time errors. You may also have functions in your workbook that depend on sheets you’re creating or deleting and you need to check if they exist.

A User Defined Function To Check If A Sheet Exists Within The Current Workbook

This is a simple VBA function that will return true if the current workbook contains a sheet with the exact name passed through the function and returns false otherwise. This function is not case sensitive so Sheet1 and SHEET1 are considered to be the same (sheet names in Excel are not case sensitive). Here the VBA is formatted as a user defined function .

With this code we can use =WorksheetExists(B3) to test any text string to see if it exists as a sheet name in the current workbook.

About the Author

John MacDougall

Subscribe for awesome Microsoft Excel videos 😃

• Pivot Table Tips and Tricks You Need to Know
• Everything You Need to Know About Excel Tables
• The Complete Guide to Power Query
• Introduction To Power Query M Code
• The Complete List of Keyboard Shortcuts in Microsoft Excel
• The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

Related Posts

7 Ways to Disable Macros in Microsoft Excel

Jan 19, 2024

Excel VBA macros undoubtedly automate repetitive tasks, ensuring swift...

How To Record a VBA Macro in Microsoft Excel

Jan 15, 2024

Do you want to automate highly repetitive yet simple tasks in Microsoft Excel?...

5 Ways to Edit Macros in Microsoft Excel

Aug 11, 2023

Do you start recording a macro from scratch if you need to modify a small...

This didn’t work for me but I got it to run by adding;

1. Dim ws = worksheet 2. Change all references from ‘Sheet’ to ws

Get the Latest Microsoft Excel Tips

Follow us to stay up to date with the latest in Microsoft Excel!

Check IF Sheet Exists Using VBA in Excel

With VBA, you can easily check whether a sheet exists or not in a given workbook by going through all the sheets and checking their names.

In this article, I am going to show you a few scenarios where you can use VBA to check if a sheet exists or not in the current workbook, in some other open workbook, or even in a closed workbook.

This Tutorial Covers:

Check If the Sheet Exists and Show a Message

Below is the VBA code that checks whether the sheet with the name sales exists in the current book or not.

The above code uses a For Next loop to go through each worksheet in the current workbook and check its name.

If the name of the worksheet is “Sales”, it exits the for loop and shows a message, as shown below.

And if it cannot find the worksheet with the name ‘Sales’, it will show another message, as shown below.

Note that in the above code, I’ve hard-coded the name of the sheet that I need to check for.

Ask the Sheet Name from the User and Check If It Exists

If you want to ask the user for the sheet name and then run the code to check for it, you can use the below code.

It first shows an input box asking for the sheet name from the user and then checks for it in the workbook.

Check If the Sheet Exists (if Not, Create It)

Below is the VBA code asks the user for the sheet name that needs to be checked for existence.

It then goes through all the worksheets and checks whether the sheet exists or not. If the sheet exists, it shows a message stating that, and if the sheet does not exist, it creates a new one with the same name.

Check If the Sheet Exists in Another Open Workbook

If you already have another workbook open and you want to check whether a sheet exists in that open workbook or not, you can use the below code.

When you run the above code, it is going to ask you for the sheet name and then go through all the sheets in the specified workbook. In this example, I have used Example.xlsx as the workbook where I am checking the sheet.

You can change the workbook name in the code, or you can use an input box to ask the user for the workbook name.

Check If the Sheet Exists in a Closed Workbook

If you need to check whether a sheet exists or not in a closed workbook, you can use the below VBA code.

The above code first asks for the sheet name that you want to check and then shows the Open File dialog box so that you can select the file in which you want to check for the existence of the sheet.

Once this is done, Excel opens the workbook in the background and then goes through all the worksheets in that workbook. If it finds the worksheet with the specified name, it shows you a message box, as shown below.

And if it isn’t able to find the worksheet, it shows you a message box that’s shown below.

It also closes the workbook in the back end once it’s done looking for the sheet.

Other articles you may also like:

• Delete Sheet in Excel Using VBA
• VBA Copy Sheet to New/Existing Workbook
• VBA Activate Sheet
• VBA Check IF Cell is Empty (ISEMPTY Function)
• Working with Worksheets using Excel VBA
• Hide or Unhide Sheets Using VBA

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Worksheet name exists

Related functions .

To test if a worksheet name exists in a workbook, you can use a formula based on the ISREF and INDIRECT functions. In the example shown, the formula in C5 is:

Generic formula

Explanation .

The ISREF function returns TRUE for a valid worksheet reference and FALSE is not.

In this case, we want to find out of a particular sheet exists in a workbook, so we construct a full reference by concatenating the sheet names in column B with an exclamation mark and "A1":

This returns the text:

which goes into the INDIRECT function. INDIRECT then tries to evaluate the text as a reference.

When INDIRECT succeeds, the reference is passed into ISREF which returns TRUE. When INDIRECT can't create a reference, it throws a #REF error, and ISREF returns FALSE.

Dealing with spaces and punctuation in sheet names

If sheet names contain spaces, or punctuation characters, you'll need to adjust the formula to wrap the sheet name in single quotes like this:

Related formulas

• Dynamic worksheet reference

• Get sheet name only

Related functions

• ISREF Function

The Excel ISREF returns TRUE when a cell contains a reference and FALSE if not. You can use the ISREF function to check for a reference in a formula.

• INDIRECT Function

The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

Get training, quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

Using Excel INDIRECT and ISREF Functions for Worksheet Verification

by Ilker | Jan 24, 2024 | Excel Tips & Tricks

This article explains how to use the INDIRECT and ISREF functions in Excel to better organize large spreadsheets. These functions help you manage multiple sheets effectively and ensure they work correctly. You'll learn the basics of INDIRECT , see examples, and discover how it improves data management.

Syntax of ISREF and INDIRECT Excel Functions

In Excel, the combination of the ISREF and INDIRECT functions is a tool for verifying the existence of a worksheet. The syntax for this combination is quite straightforward yet functional.

=ISREF(INDIRECT("name of sheet that you want to check & "!A1"))

INDIRECT Function: The ` INDIRECT ` function is a dynamic function in Excel that converts a text string into a valid cell reference. When you use ` INDIRECT ` in Excel, especially in this context, it generates a reference to a specific cell in another worksheet. In our formula, ` INDIRECT("name of the sheet that you want to check & "!A1")` creates a reference to cell A1 of the specified worksheet. The 'name of the sheet that you want to check' should be replaced with the actual name of your target sheet. Understanding how to use INDIRECT in Excel is important, as this function forms the core of our worksheet verification method.

Concatenation for Sheet Reference: The `& "!A1"` part of the ` INDIRECT ` function is where you concatenate the worksheet name with a cell reference. In this case, we're checking cell A1 , which can be any reference. This concatenation is critical in constructing the complete cell reference for the ` INDIRECT ` function.

ISREF Function: Excel's ` ISREF ` function checks if a value is a valid reference. It returns TRUE if the supplied value is a reference and FALSE otherwise. In our formula, ` ISREF ` checks whether the reference created by the ` INDIRECT ` function is valid. This effectively tells us if the specified worksheet exists.

This method is especially useful when dealing with large workbooks with multiple sheets, ensuring you avoid errors related to non-existent references.

Steps to Utilize ISREF and INDIRECT Excel Functions

When working with complex Excel workbooks, particularly those utilizing the INDIRECT and ISREF functions, verifying a sheet's existence is essential. This process can be seamlessly achieved by following these structured steps, which utilize the INDIRECT function in Excel and the ISREF function:

• Initiate the ISREF Function: Begin by activating the cell where you want the verification result to appear. Type ` =ISREF( `. This is the starting point of our formula, where the ISREF function in Excel is used to check if a specified reference is valid.
• Incorporate the INDIRECT Function: Immediately after `=ISREF(, `continue with the INDIRECT function by typing `INDIRECT(. ` This formula portion is crucial, as the INDIRECT Excel function dynamically converts text into a cell reference. Understanding how to use INDIRECT in Excel is pivotal, as this function allows for a flexible reference to your desired sheet.
• Specify the Sheet Reference: It's time to input the sheet reference. For this, select or type in the range reference that contains the sheet name, for example, ` B3 `. This should look like ` INDIRECT(B3 `. Here, B3 ideally contains the name of the sheet you wish to check. Using INDIRECT to reference another sheet is a powerful feature of Excel.
• Concatenate with Cell Reference: Continue the formula by adding ` &"!A1" `. This concatenates the sheet name in B3 with a specific cell reference ( A1 in this case) , making a complete reference for the INDIRECT function.
• After completing your formula with double parentheses )),  press Enter. Your final formula should look like ` =ISREF(INDIRECT(B3&"!A1")) `. This will return TRUE if the specified sheet exists and the cell A1 is a valid reference or FALSE if not.

ISREF Function: Excel's Reference Verifier

In Excel, the ISREF function plays n important role in verifying references. It returns a Boolean value ( TRUE or FALSE ) indicating whether its argument is a valid reference. This capability is particularly useful for confirming the existence of worksheets in your Excel workbook. Directing ISREF to a cell reference in a specified worksheet, such as ' A1 ' in ' Sheet1 ' or ' New Sheet' can determine if that worksheet exists.

Example Usage for ISREF Function

ISREF(Sheet1!A1)ISREF('New Sheet'!A1) However, manually typing references for each sheet can be cumbersome and inefficient. This is where combining ISREF with another powerful function, INDIRECT , becomes advantageous.

INDIRECT Function: Excel's Dynamic Reference Creator

The INDIRECT function in Excel is designed to convert text strings into valid references. It's invaluable for scenarios where you must alter cell references within a formula without modifying it. For instance:

INDIRECT(Sheet1!A1)

INDIRECT('New Sheet'!A1)

Dynamic Worksheet Verification with ISREF and INDIRECT

When combined, ISREF and INDIRECT provide a dynamic solution to verify the existence of worksheets in Excel. ISREF checks if a reference exists, while INDIRECT converts text strings into those references. To construct a valid reference string, it’s essential to append a cell reference (like ' A1 ') to the worksheet name. Remember, an exclamation mark is crucial for separating the worksheet name from the cell reference.

Example of Combined Use

Standard Reference Check:

=ISREF(INDIRECT(B3&"!A1"))

Special-Character-Safe Version:

=ISREF(INDIRECT("'"&B3&"'!A1"))

In this version, wrapping the sheet name in single quotes within the INDIRECT function ensures that the formula remains effective even if the worksheet name contains special characters or spaces.

Related posts

Recent Posts

• Building an Interactive Web Application for Searching and Filtering Data in Excel
• Step-by-Step Guide: Creating a Sports Tournament Template in Excel and Bringing It Online
• From Data to Insights: How to Create Impactful Reports and Dashboards Using Excel
• What's New in SpreadsheetWeb? - June 2024
• Create a UEFA Euro 2024 Score Guessing App with Daily Leaderboard Updates

AutoMacro: Ultimate VBA Add-in

Read all reviews

Return to VBA Code Examples

Check if Sheet and/or Range Exists Function

Editorial Team

Reviewed by

Steve Rynearson

In this Article

Check if Sheet Exists

Check if range exists on a sheet, adjusting the rangeexists function.

We’ve created a function that will test if a Sheet or Range (on a particular sheet) exists. The Range test is useful if you want to check if a particular named range exists on a sheet.

Place the function within a VBA Code Module and you can access it by using sub procedures like these:

Check if Sheet Exists on Another Workbook

The above function looked at ActiveWorkbook (the currently active workbook). Instead you could adjust the Function to look at a specific workbook like this:

Implementation:

VBA Coding Made Easy

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

AutoMacro: VBA Add-in with Hundreds of Ready-To-Use VBA Code Examples & much more!

• Advertise With Us
• Excel Forum
• Commercial Services

Determine if a sheet exists in a workbook using VBA in Microsoft Excel

There could be a time when you have to check if a worksheet, which you have create or deleted in a workbook in a VBA macro / code, exists. We can do this easily using a function / macro. There are multiple ways of checking if a worksheet exists.

We will cover the following ways in this article:

1. User Defined Function known as UDF 2. Sub routine through message box

First option: User Defined Function

Following snapshot contains few sheets names & we will check if the names of sheet in column A exist.

To find if a specific sheet exists, we need to follow the below steps to launch VB editor

• Click on Developer tab
• From Code group select Visual Basic

• Copy the below code in the standard module

• In order to check, we will use UDF in cell B2 as
• =WorksheetExists(A2)

• In the above image, “MasterSheet” does not exist in our sample workbook; hence, formula has given answer as False

Code Explanation:

This function takes the value for “WorksheetName” from the macro which performs other activities. If you need to change it as per your code, you may.

For Each Sht In ThisWorkbook.Worksheets and Next Sht are the starting and ending parts of the loop respectively.

Then If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then

            WorksheetExists = True

Checks if the Sheet name is matching the Sheet name passed from the main macro. If it does, WorksheetExists is True, and we can exit the function. Otherwise, WorksheetExists = False is returned back to the main macro. The loop goes from the 1st sheet to the next until all the sheets have been checked.

Second Option: Sub routine through message box

We can have a normal subroutine which is calling a UDF and, if the specified sheet is found, the message box will display, ‘sheet exist’; if not found, then msgbox pops up, ‘sheet not found’.

To check, we will copy the following code in the standard module:

After running the macro “FindSheet”, we will get the following message box if sheet exists:

If Sheet does not exist we will get the following message box:

Similarly, we can have a simple IF loop which checks if the sheet exists and performs certain actions thereafter.

• To test the above macro, we need to create a sheet name “Main”. This macro is very simple
• It loops through each worksheet in the workbook
• Then it checks if the worksheet name is not MAIN
• If it is MAIN, it displays text, like “MAIN LOGIN PAGE” in A1 of that sheet, else it displays the name of the sheet in cell A1

• This is just another way of checking if the sheet exists. If it exists, perform action A, if not, action B

Conclusion: We can identify whether a sheet exists in our workbook or not; we can use UDF or subroutine as per our convenience.

If you liked our blogs, share it with your friends on Facebook . And also you can follow us on Twitter and Facebook .

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]

I need some code in the macro below to exit the sub if the sheet has already been created. I must be getting too old I can't get my mind around it ......thanks Robbo

Sub CopyBingosheet() ' ' CopyBingosheet Macro ' Copy blank sheet and rename as the date '

Sheets("Summary Sheet").Visible = True Sheets("Menu").Select Sheets("bingo sheet").Visible = True Sheets("Summary Sheet").Select Sheets("bingo sheet").Select Sheets("bingo Sheet").Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count) Sheets("bingo sheet (2)").Select Sheets("Bingo Sheet (2)").Name = Format(Date, "dd-mm-yyyy")

Range("Q16").Select ActiveCell.FormulaR1C1 = "=TODAY()" Sheets("bingo sheet").Select ActiveWindow.SelectedSheets.Visible = False Range("A6").Select ActiveWorkbook.Save End Sub

I want to Copy the data related to multiple Accounts into the different workbook which contains the Multiple tabs Account wise into the respective account tab,

Is there a reason this needs to be done this way? I use this within a Sub AddSheet() and it works @here:

Dim Answer$ Answer = Application.InputBox("Enter new Sheet name") If Answer = "False" Then Exit Sub On Error GoTo Continue: Sheets(Answer).Activate MsgBox "Sheet Exists" Exit Sub Continue: ~~~rest of Sub AddSheet()

I need support for, i have many excel workbook in a folder need to open on by one to take some data in one particular sheet which contain many 3 or 4 sheet, for ex.... data fetching from "My name" sheet if this sheet does not exist how to close this workbook and go to open next one same way continuously.... pls tell me code for this. Thanks in advance.

Although your solution works, you should never use errors to control the flow of a program. A more correct approach is:

Function WorksheetExists(ByVal WorksheetName As String) As Boolean Dim Sht As Worksheet

For Each Sht In ThisWorkbook.Worksheets If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then WorksheetExists = True Exit Function End If Next Sht

WorksheetExists = False End Function

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

To avoid automated spam,Please enter the value * 1 × nine =

Related Excel Tips

Determine if a workbook exists using VBA in M...

Searching and getting value from external wor...

Add And Save New Workbook Using VBA In Micros...

How to Delete Sheets Without Confirmation Pro...

Combine Multiple Worksheets into One...

• Basic Excel
• Excel 365 Functions
• Excel Business Templates and Dashboards
• Excel Dashboards
• Excel Date and Time
• Excel Errors
• Excel Functions
• Excel Functions List
• Excel General
• Excel Macros and VBA
• Excel Spanish
• Excel Text, Editing and Format
• Excel Tips and Tricks

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

• Excel Editing
• Excel Format

Get latest updates from exceltip in your mail.

Google serves cookies to analyse traffic to this site. Information about your use of our site is shared with Google for that purpose

Privacy Overview

Strictly necessary cookies.

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

You can adjust all of your cookie settings by navigating the tabs on the left hand side.

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.

VBA Function To Determine If A Worksheet Exists In A Workbook

What This VBA Code Does

The following VBA macro code provides a function that tests if a worksheet exists in the ActiveWorkbook. Simply feed the function the name of the worksheet you would like to test and the function will output either a True/False result.

This is a great way to prevent errors with larger pieces of macro code if your routines involve worksheet names that are static or require user inputs.

Using VBA Code Found On The Internet

Now that you’ve found some VBA code that could potentially solve your Excel automation problem, what do you do with it? If you don’t necessarily want to learn how to code VBA and are just looking for the fastest way to implement this code into your spreadsheet, I wrote an article (with video) that explains how to get the VBA code you’ve found running on your spreadsheet.

Getting Started Automating Excel

Are you new to VBA and not sure where to begin? Check out my quickstart guide to learning VBA . This article won’t overwhelm you with fancy coding jargon, as it provides you with a simplistic and straightforward approach to the basic things I wish I knew when trying to teach myself how to automate tasks in Excel with VBA Macros.

Also, if you haven’t checked out Excel’s latest automation feature called Power Query , I have put together a beginner’s guide for automating with Excel’s Power Query feature as well! This little-known built-in Excel feature allows you to merge and clean data automatically with little to no coding!

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you:  My Guide to Getting the Solution to your Problems FAST!  In this article, I explain the best strategies I have come up with over the years to get quick answers to complex problems in Excel, PowerPoint, VBA,  you name it ! 

I highly recommend that you check this guide  out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee that 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Keep Learning

Loop Through All Currently Open Workbooks And Modify Them

What This VBA Code Does This bit of VBA code shows you how to loop through all currently open Excel...

Loop Through All Chart Sheets In An Excel Workbook

What This VBA Code Does There are two types of Sheets within an Excel Workbook. Worksheets Chart Sheets The below...

VBA Code To Clear Cells Not Containing A Specific Word Or Phrase

What This VBA Code Does This brief macro code with go through each cell in a designated range and determines...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

VBA: How to Check if Sheet Exists (With Example)

You can create the following function in VBA to check if a particular sheet exists in the currently active Excel workbook:

This function will return either TRUE or FALSE to indicate whether or not a particular sheet name exists in the currently active Excel workbook.

Note that this function simply checks if the index number of a sheet is greater than 0.

If the sheet exists, the index number of the sheet will have a value of 1 at the minimum, which will cause the function to return a value of TRUE .

The following example shows how to use this function in practice.

Example: Use VBA to Check if Sheet Exists

Suppose we have the following Excel workbook with three sheets:

We can create the following function in VBA to check if a particular sheet name exists in this workbook:

We can then type the following formula into cell A1 of the currently active sheet to check if the sheet name “Teams” exists in this workbook:

The following screenshot shows how to use this formula in practice:

The function returns TRUE  since this sheet name does exist in the workbook.

Also note that this function is not case-sensitive.

For example, if we checked if the sheet name “teams” exists then the function would also return TRUE :

However, suppose we check if the sheet name “coaches” exists:

The function returns FALSE since this sheet name does not exist in the workbook.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Number of Sheets in Workbook VBA: How to Extract Data from Another Workbook VBA: How to Insert Multiple Rows

Featured Posts

Hey there. My name is Zach Bobbitt. I have a Masters of Science degree in Applied Statistics and I’ve worked on machine learning algorithms for professional businesses in both healthcare and retail. I’m passionate about statistics, machine learning, and data visualization and I created Statology to be a resource for both students and teachers alike.  My goal with this site is to help you learn statistics through using simple terms, plenty of real-world examples, and helpful illustrations.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Join the Statology Community

Sign up to receive Statology's exclusive study resource: 100 practice problems with step-by-step solutions. Plus, get our latest insights, tutorials, and data analysis tips straight to your inbox!

By subscribing you accept Statology's Privacy Policy.

Stack Exchange Network

Stack Exchange network consists of 183 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

How can I tell if sheet a exists in a workbook?

I have the following code that's not working as expected:

How can I tell if sheet a exists in the workbook?

• microsoft-excel
• worksheet-function

5 Answers 5

I'd make a separate function for it:

Then you can easily call it where needed, even in a formula if you wanted:

• The use of GoTo and labels in structured programming is considered bad form and can usually be avoided with a little extra thought. –  Andrew P. Commented Aug 24, 2017 at 13:49
• 1 @AndrewP. VBA error handling consists of GoTo or Resume, both of these are far from wonderful, but I've always felt I have more control over a GoTo than a Resume as a Resume could easily pass unnoticed without a conditional check on an error result. I'm always willing to learn though, so I'm looking forward to seeing your answer on the correct way to handle this :) –  Jonno Commented Aug 24, 2017 at 14:01

Something like:

• I like the descriptive sub name =) –  Jonno Commented Feb 16, 2016 at 17:50
• @Jonno ....................it's fixed................ –  Gary's Student Commented Feb 16, 2016 at 18:10

You can check for the error. eg:

In the template that I made for AutoFilter, I used a function and procedure to check whether a worksheet is available. In the codes below, it is checked whether the DATA sheet is in the workbook :

When the Sht_Cntrl procedure is called ( Call Sht_Cntrl ) it is created if DATA sheet does not exist.

enter image description here

You can view the codes by downloading the sample template from this page : Vba autofilter with listbox

this works as expected

• 2 Unless it doesn't exist, then it may throw a subscript out of range error? –  Jonno Commented Feb 16, 2016 at 17:51

You must log in to answer this question.

Not the answer you're looking for browse other questions tagged microsoft-excel worksheet-function vba ..

• The Overflow Blog
• Community Products Roadmap Update, July 2024
• Featured on Meta
• We spent a sprint addressing your requests — here’s how it went
• Upcoming initiatives on Stack Overflow and across the Stack Exchange network...

Hot Network Questions

• Who first promoted the idea that the primary purpose of government is to protect its citizens?
• Spec sheet on Shimano FC-C201 seemingly does not match my bike
• If a lambda is declared as a default argument, is it different for each call site?
• Did any 8-bit machine select palette by character name instead of color memory?
• Why do I see low voltage in a repaired underground cable?
• Avoid underset text overlaping
• My previously healthy avocado plant was pruned bare and has since been turning brown on top
• Align 3 tables neatly
• Reversing vowels in a string
• Evil God Challenge: What if an evil god is just trolling humanity and that explains why there's good in the world?
• Seeing edges where there are no edges
• Why does `p` not put all yanked lines when copying across files?
• Where is the pentagon in the Fibonacci sequence?
• Rear disc brakes work intermittently
• mirrorlist.centos.org no longer resolve?
• What is the translation of "a discrete GPU" in French?
• Cliffhanger ending?
• Does a Chad citizen need a transit visa in Brazil?
• Will 2.1" schwalbe MTB tire in 25mm rim become wider that 2.25" in 19mm rim?
• What is meant by "I was blue ribbon" and "I broke my blue ribbon"?
• Don't make noise. OR Don't make a noise
• Is it possible to "label" Segwit spendable output ScriptPubKeys with arbitrary bytes?
• Can the U. S. Supreme Court decide on agencies, laws, etc., that are not in front of them for a decision?
• How well does the following argument work as a counter towards unfalsifiable supernatural claims?

• Forgotten Your Password?

• Today's Posts
• Mark Forums Read
• Quick Links :
• What's New?
• Members List

Forum Rules

• Commercial Services
• Advanced Search

• Microsoft Office Application Help - Excel Help forum
• Excel Programming / VBA / Macros
• [SOLVED] Check if a worksheet exists

Check if a worksheet exists

Thread tools.

• Show Printable Version
• Subscribe to this Thread…
• Mark this thread as unsolved…

Rate This Thread

• Current Rating
• ‎ Excellent
• ‎ Average
• ‎ Terrible
• Linear Mode
• Switch to Hybrid Mode
• Switch to Threaded Mode

Hi all I am looking for a way to get VBA to check if a worksheet exists using a named range as the source and if the sheet does not exist then add the required sheet name... the only examples I have been able to find use the following code If SheetEsists("sheetname") = True Then. However, this does not appear to be a known function within Excel 2000..... Is there another way or am I missing something? Regards and Thanks Mort_kombat

RE: Check if a worksheet exists

Function SheetExists(ByRef SheetName As String) As Boolean Dim ws As Worksheet SheetExists = False For Each ws In Worksheets If ws.Name = SheetName Then SheetExists = True Next End Function HTH. --Bruce "Mort_Komabt" wrote: > Hi all > > I am looking for a way to get VBA to check if a worksheet exists using a > named range as the source and if the sheet does not exist then add the > required sheet name... the only examples I have been able to find use the > following code > If SheetEsists("sheetname") = True Then. > > However, this does not appear to be a known function within Excel 2000..... > Is there another way or am I missing something? > > Regards and Thanks > > Mort_kombat

Thanks for that..... It works great "bpeltzer" wrote: > Function SheetExists(ByRef SheetName As String) As Boolean > Dim ws As Worksheet > SheetExists = False > For Each ws In Worksheets > If ws.Name = SheetName Then SheetExists = True > Next > End Function > HTH. --Bruce > > "Mort_Komabt" wrote: > > > Hi all > > > > I am looking for a way to get VBA to check if a worksheet exists using a > > named range as the source and if the sheet does not exist then add the > > required sheet name... the only examples I have been able to find use the > > following code > > If SheetEsists("sheetname") = True Then. > > > > However, this does not appear to be a known function within Excel 2000..... > > Is there another way or am I missing something? > > > > Regards and Thanks > > > > Mort_kombat

Re: Check if a worksheet exists

Hi Mort, You could add a new sheet automatically if the value in your named range changes. Try this in the worksheet module of the changing cell. Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim sName As String Dim bShtAdded As Boolean If Target(1).Address = Range("myName").Address Then sName = Range("myName").Value If Len(sName) > 1 Then On Error Resume Next Set ws = Worksheets(sName) On Error GoTo errH If ws Is Nothing Then Set ws = Worksheets.Add bShtAdded = True ws.Name = sName End If End If End If Exit Sub errH: If bShtAdded Then MsgBox "Cannot name new sheet : " & sName End If End Sub If your named range is a formla cell, amend the above to check for changes in whatever value cell changes the result in your formula, eg your formula =A! If Target(1).Address = "$A$1" Then If you only want to add a new sheet by calling a normal macro, Sub MyMacro() ' all the code as above but delete If Target(1).Address = Range("myName").Address Then end if End Sub Regards, Peter T "Mort_Komabt" < [email protected] > wrote in message news: [email protected] ... > Hi all > > I am looking for a way to get VBA to check if a worksheet exists using a > named range as the source and if the sheet does not exist then add the > required sheet name... the only examples I have been able to find use the > following code > If SheetEsists("sheetname") = True Then. > > However, this does not appear to be a known function within Excel 2000..... > Is there another way or am I missing something? > > Regards and Thanks > > Mort_kombat

More efficiently '----------------------------------------------------------------- Function SheetExists(Sh As String, _ Optional wb As Workbook) As Boolean '----------------------------------------------------------------- Dim oWs As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing) On Error GoTo 0 End Function -- HTH RP (remove nothere from the email address if mailing direct) "Mort_Komabt" < [email protected] > wrote in message news: [email protected] ... > Thanks for that..... It works great > > "bpeltzer" wrote: > > > Function SheetExists(ByRef SheetName As String) As Boolean > > Dim ws As Worksheet > > SheetExists = False > > For Each ws In Worksheets > > If ws.Name = SheetName Then SheetExists = True > > Next > > End Function > > HTH. --Bruce > > > > "Mort_Komabt" wrote: > > > > > Hi all > > > > > > I am looking for a way to get VBA to check if a worksheet exists using a > > > named range as the source and if the sheet does not exist then add the > > > required sheet name... the only examples I have been able to find use the > > > following code > > > If SheetEsists("sheetname") = True Then. > > > > > > However, this does not appear to be a known function within Excel 2000..... > > > Is there another way or am I missing something? > > > > > > Regards and Thanks > > > > > > Mort_kombat

Thread Information

Users browsing this thread.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
• BB code is On
• Smilies are On
• [IMG] code is Off
• HTML code is Off
• Trackbacks are Off
• Pingbacks are Off
• Refbacks are Off
• ExcelForum.com

Check if an Excel worksheet exists with the same name and then delete the worksheet

Check if an excel worksheet exists with the same name and then delete the worksheet using vba.

VBA EXPLANATION

METHOD 1. Check if an Excel worksheet exists with the same name and then delete the worksheet using VBA

VBA Sub Check_if_Worksheet_exists_then_Delete_Worksheet() 'declare variables Dim ws As Worksheet Dim check As Boolean For Each ws In Worksheets If ws.Name Like "Data" Then check = True : Exit For Next If check = True Then

METHOD 2. Check if an Excel worksheet exists with the same name and then delete the worksheet using VBA

VBA Sub Check_if_Worksheet_exists_then_Delete_Worksheet() 'declare a variable Dim ws As Worksheet On Error Resume Next Set ws = Worksheets("Data") On Error GoTo 0 If Not ws Is Nothing Then

Explanation about how to check if a worksheet exists with the same name and then delete the worksheet

VBA Methods: Using VBA you can check if a worksheet exists with the same name and then delete the worksheet. The VBA code will go through the worksheets in the nominated workbook until it finds a worksheet with the exact name and then it will delete the worksheet. Alternatively, if it can't find a worksheet with the same name after it went through all of the worksheets, it will display a message box stating "Worksheet Name Doesn't Exist".

45,000+ students realised their study abroad dream with us. Take the first step today

Meet top uk universities from the comfort of your home, here’s your new year gift, one app for all your, study abroad needs, start your journey, track your progress, grow with the community and so much more.

Verification Code

An OTP has been sent to your registered mobile no. Please verify

Thanks for your comment !

Our team will review it before it's shown to our readers.

• Parts of Speech /

Verb Cheat Sheet: Types of Verbs Explained in Detail (Download PDF)

• Updated on  
• Jul 3, 2024

Verbs are the workhorses of any sentence! They bring life and action to your writing, telling you what’s happening or what exists. Verbs are words that express an action (run, jump, eat), an occurrence (happen, exist), or a state of being (seem, appear). Continue reading this blog post to find all the information about the verb cheat sheet and how you can incorporate that into your writing in English .

This Blog Includes:

Irregular verb cheat sheet, action verb cheat sheet, subject verb agreement cheat sheet .

Learn English from scratch here!

Irregular verbs in English don’t follow the usual pattern of adding “- ed ” to form the past tense and past participle. For example , the regular verb “walk” becomes “walked” (past tense) and had “walked” (past participle), but the irregular verb “eat” becomes “eaten” (past tense) and had “eaten” (past participle).

One must note that this type of verb is one of the hardest parts to study in the modern English language. It comes with no specific formula so English speakers have no choice but to memorise each word along with the special verb forms.

To help you, we have compiled a quick cheat sheet on irregular verbs. Do check it out. 

Also Read: All about Regular and Irregular Verbs

Action verbs are the workhorses of a sentence. They describe what the subject of the sentence is doing . They create movement and bring your writing to life. Here are some key points about action verbs:

• They describe physical actions: This is the most straightforward type of action verb. Running, jumping, eating, and throwing are all examples.
• They describe mental actions: Action verbs can also represent what someone is thinking or feeling. Examples include considering, analysing, dreaming, and worrying.

Additionally, action verbs are known to enhance one’s writing. This is done in the following ways: 

• Lively Sentences: Action verbs make your writing more engaging and interesting to read.
• Clarity: They clearly show what’s happening in your story or explanation.
• Strong Voice: Action verbs help you write with a strong and active voice.

To help you with the formation of action verbs, we have compiled this cheat sheet. Do check this out. 

Also Read: Nouns vs Verbs: Difference You Must Know! 

Subject-verb agreement is a fundamental rule in English grammar that ensures your sentences sound correct and clear. It essentially means that the verb in your sentence needs to match the subject in number (singular or plural).

Here’s a breakdown of subject-verb agreement of all the rules:

• Singular Subject + Singular Verb: If your subject is singular, the verb needs to be singular as well. (Example: The cat sleeps soundly.)
• Plural Subject + Plural Verb: If your subject is plural, the verb needs to be plural as well. (Example: The cats sleep soundly.)

For more clarification, check out the PDF below which will help you with the formation of the sentences:

Also Read: Subject-Verb Agreement: 12 Rules & Examples

Check Out More Blogs on Learn English!

We utilise verbs to describe states of being (how things are) and actions (what things do). Describe, eat, and rotate are a few examples of verbs. 

A verb is called an action word that shows an action (sing), occurrence (develop), or state of being (exist). 

This was all about the verb cheat sheet and related information. Hope you understand the concept and know how to proceed. You can also follow the Learn English page of Leverage Edu for more exciting and informative blogs related to grammar. 

Malvika Chawla

Malvika is a content writer cum news freak who comes with a strong background in Journalism and has worked with renowned news websites such as News 9 and The Financial Express to name a few. When not writing, she can be found bringing life to the canvasses by painting on them.

Leave a Reply Cancel reply

Save my name, email, and website in this browser for the next time I comment.

Contact no. *

Leaving already?

8 Universities with higher ROI than IITs and IIMs

Grab this one-time opportunity to download this ebook

Connect With Us

45,000+ students realised their study abroad dream with us. take the first step today..

Resend OTP in

Need help with?

Study abroad.

UK, Canada, US & More

IELTS, GRE, GMAT & More

Scholarship, Loans & Forex

Country Preference

New Zealand

Which English test are you planning to take?

Which academic test are you planning to take.

Not Sure yet

When are you planning to take the exam?

Already booked my exam slot

Within 2 Months

Want to learn about the test

Which Degree do you wish to pursue?

When do you want to start studying abroad.

January 2024

September 2024

What is your budget to study abroad?

How would you describe this article ?

Please rate this article

We would like to hear more.

Have something on your mind?

Make your study abroad dream a reality in January 2022 with

India's Biggest Virtual University Fair

Essex Direct Admission Day

Why attend .

Don't Miss Out

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Check if an excel cell exists on another worksheet in a column - and return the contents of a different column

What I want to do is to say if the contents of cell D3 (on current worksheet) exist in column A in the first worksheet (in my case entitled list). (and they always do exist somewhere). Return the contents of the corresponding row in Column C .

In other words if the matching cell is found in Row 12 - return data from C12 .

I've used the following syntax but I can't seem to get the last part to work correctly.

How to fix the formula?

• excel-formula
• excel-match

You can use following formulas.

For Excel 2007 or later:

For Excel 2003:

• I'm using List!A:C in VLOOKUP and returns value from column № 3
• I'm using 4th argument for VLOOKUP equals to FALSE , in that case VLOOKUP will only find an exact match, and the values in the first column of List!A:C do not need to be sorted (opposite to case when you're using TRUE ).

• It worked! =IFERROR(VLOOKUP(D3,List!A:C,3,FALSE),"No Match") I'm not entirely sure i understand the List!A:C (the A:C) part. I thought the function worked as such - find the data in one column and return the contents of another (What if i only want it to search in Column A and always return Column C or D ... but never search in those columns). –  YelizavetaYR Commented Apr 14, 2014 at 14:04
• 1 List!A:C,3, means that yor're going to search in first column of range List!A:C (i.e. List!A:A ) and return corresponding value from third column of range List!A:C (i.e. List!C:C ) –  Dmitry Pavliv Commented Apr 14, 2014 at 14:07
• Years later, still a fantastic solution. Thanks! –  hockey2112 Commented Feb 17, 2020 at 22:58

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged excel excel-formula vlookup excel-match or ask your own question .

• The Overflow Blog
• Community Products Roadmap Update, July 2024
• Featured on Meta
• We spent a sprint addressing your requests — here’s how it went
• Upcoming initiatives on Stack Overflow and across the Stack Exchange network...
• Policy: Generative AI (e.g., ChatGPT) is banned
• The [lib] tag is being burninated
• What makes a homepage useful for logged-in users

Hot Network Questions

• Evil God Challenge: What if an evil god is just trolling humanity and that explains why there's good in the world?
• Sitting on a desk or at a desk? What's the diffrence?
• How to manage talkover in meetings?
• How shall I find the device of a phone's storage so that I can mount it in Linux?
• Avoid underset text overlaping
• Why does Paul's fight with Feyd-Rautha take so long?
• Are US enlisted personnel (as opposed to officers) required, or allowed, to disobey unlawful orders?
• Why does independent research from people without formal academic qualifications generally turn out to be a complete waste of time?
• Can the US president kill at will?
• Why is pressure in the outermost layer of a star lower than at its center?
• GDPR Data Processor
• cheapest ways to hedge
• Making a node in TikZ occupy the vertical space of one line - at least as far as positioning a "pin" is concerned
• If a lambda is declared as a default argument, is it different for each call site?
• Why does redirecting stderr interfere with bash's handling of $COLUMNS and the `checkwinsize` option?
• How does the temperature of the condenser impact an air conditioner's energy usage?
• Is intuitionistic mathematics situated in time?
• Do thermodynamic cycles occur only in human-made machines?
• My previously healthy avocado plant was pruned bare and has since been turning brown on top
• Strange Interaction with Professor
• Why should I meet my advisor even if I have nothing to report?
• Solving complex opamp circuits
• What type of interaction in a π-complex?
• Airtight beaks?