how to get the worksheet name in vba

How to Get Sheet Name Using VBA (With Example)

You can use the following methods in VBA to get the name of a specific sheet in an Excel workbook:

Method 1: Get Name of Active Sheet

This function will return the name of the active sheet.

Method 2: Get Name of Sheet by Number

This function will return the name of the n th sheet that you specify.

The following examples show how to use each function in practice with the following Excel workbook that has four sheets:

Example 1: Use VBA to Get Name of Active Sheet

We can create the following function in VBA to get the name of the currently active sheet in an Excel workbook:

Suppose the sheet named stats is currently active.

We can type the following formula into cell E1 of this sheet to return the name of the active sheet:

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

The function returns the value stats , since this is the name of the sheet that is currently active.

Example 2: Use VBA to Get Name of Sheet by Number

We can create the following function in VBA to get the name of the n th sheet in an Excel workbook:

Once we’ve created this function, we can then type the following formula into cell E1 of the currently active sheet to return the name of the second sheet in the workbook:

The function returns the value team , since this is the name of the second sheet 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

Published by Zach

Save Up to 85% LIMITED TIME OFFER

Get Active Workbook or Worksheet Name Path FullName in Excel VBA

Get an Active Workbook or Worksheet Name

Vba code library, vba reference, vba projects, full access with source code.

Designed and Developed by PNRao

Full Access with VBA Source Code

Well Commented Codes Lines

Creative and Professional Design

Effortlessly Manage Your Projects

120+ project management templates.

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:

50+ Excel Templates

50+ PowerPoint Templates

25+ Word Templates

Description:

When we are working with workbooks and worksheets, some times we may required to Get Active Workbook or Worksheet Name, Path of the workbook to know the directory, FullName(Complete path) of the workbook to know the location of the workbook, selected Range address in active sheet or selected Cell address in active sheet using Excel VBA.

Solution(s):

You can get Active Workbook Or Worksheet Name by using Name property of the workbook or worksheet.

Get Active Workbook or Worksheet Name – Example Cases:

Get an active workbook name, get an active workbook path, get an active workbook fullname, get an active worksheet name.

Get an Active Range Address
Get an Active Cell Address

You can use ActiveWorkbook property to return the active workbook name. You can use the following code to get the name of the Active Workbook

Instructions:

Open an excel workbook
Press Alt+F11 to open VBA Editor
Insert a new module from Insert menu
Copy the above code and Paste in the code window
Press F5 to see the output
You should see output as shown above
Save the file as macro enabled workbook

You can use ActiveWorkbook property to return the active workbook Path.You can use the following code to Get Active Workbook Path to know the workbook directory.

You can use ActiveWorkbook property to return the active workbook FullName. You can use the following code to get Active Workbook FullName to know the location of workbook.

You can use ActiveSheet property to return the ActiveSheet Name. You can use the following code to get Active Worksheet Name.

Get an Active(Selected) Range Address

You can use Address property of the selected range(Selection Method). You can use the following code to get the selected range address in active sheet.

Select a range from B2 to E11 in active sheet
Goto code window and Press F5 to see the output

Get an Active(Selected) Cell Address

You can use Address property of the selected cell(Selection Method). By using the following code you can get the selected cell address in active sheet.

Select a cell F5 in active sheet

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

All-in-One Pack 120+ Project Management Templates

Essential pack 50+ pm templates, excel pack 50+ excel pm templates, powerpoint pack 50+ excel pm templates, ms word pack 25+ word pm templates, ultimate project management template, ultimate resource management template, project portfolio management templates, related posts.

VBA ActiveSheet – Excel Active Sheet Object

Excel VBA ColorIndex

Excel VBA Copy Range to Another Sheet with Formatting

One comment.

thank you this is helpful

Project Management

Download Free Excel 2007, 2010, 2013 Add-in for Creating Innovative Dashboards, Tools for Data Mining, Analysis, Visualization. Learn VBA for MS Excel, Word, PowerPoint, Access, Outlook to develop applications for retail, insurance, banking, finance, telecom, healthcare domains.

Excel Courses
Free Excel Courses
Excel Video Tutorials
Excel Keyboard Shortcuts

Premium Excel Course Now Available!

Build professional - unbreakable - forms in excel, 45 tutorials - 5+ hours - downloadable excel files, get the name of a worksheet in macros vba in excel, black friday sale (65%-80% off).

Excel Courses Online Video Lessons Excel Guides

VBA Course (80% Off)

Forms Course (65% Off)

Email Course (65% Off)

How to get the name of a worksheet in Excel using VBA and Macros and also how to store that name in a variable for later use.

This is rather simple but there are a couple different ways to do it.

First, we can reference the currently active worksheet and put Name after it:

This gets us the name of the currently active sheet, which is also usually the currently visible sheet.

Now, to store this in a variable, just do this:

To get the name of any sheet in the workbook we can use the index number to find it like this:

Here, the 2 means that we want to access the second worksheet in the workbook. 1 would mean the first worksheet in the workbook and Name once again means that we want to get the name of it.

This method is very useful when looping through the worksheets since you know that you can always just start at 1 and add 1 until you get to the end.

To put the name in a variable, we do like we did before:

Note that you can name the variable anything you want as long as it doesnt conflict with a VBA function. So, you dont have to use shtName as your variable.

Here is a screenshot of the macro with two message boxes that output the names of the sheets:

You can copy and paste this macro from the attached workbook, located below this tutorial or in the side-bar.

This shows you how simple it is to get the name of worksheets in VBA and I hope you found it helpful!

Question? Ask it in our Excel Forum

Excel vba course - from beginner to expert.

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA Course - Beginner to Expert

Subscribe for weekly tutorials, bonus: subscribe now to download our top tutorials ebook.

The link to our top 15 tutorials has been sent to you, check your email to download it!

(If you don't see the email, check your Spam or Promotions folder and make sure to add us as a contact so you get our emails in the future.)

200+ Video Lessons 50+ Hours of Video 200+ Excel Guides

How to Search Sheet Name with VBA in Excel (3 Examples)

In this article, we will learn to search the sheet name with VBA in Excel. VBA stands for Visual Basic for Applications. Sometimes, our Excel sheet contains a large number of sheets and we need to search for a specific sheet. It is very time-consuming if we try to search the sheet name manually. So, today, we will use VBA to search the sheet name in Excel. In this article, we are going to demonstrate 3 important VBA examples.

How to Search Sheet Name with VBA in Excel: 3 Examples

To explain these examples, we will use a dataset that contains information about the Department and the Age of some employees.

VBA to Search Sheet Name in Excel and Display Search Result

1. Excel VBA to Search Sheet Name and Display Search Result

In the first example, we will use VBA to search the sheet name . Our desired sheet name is Display Result . The workbook contains many other different sheets along with the desired one. In this case, we will search the sheet name and display the result in a message box.

Let’s follow the steps below to learn this example.

Go to the Developer tab and select Visual Basic . The Visual Basic window will appear.
Alternatively, you can use the keyboard shortcut for it. Press Alt + F11 to open the Visual Basic window.

Next, select Insert .
Then click on Module from the drop-down menu in the Visual Basic window. This will open a Module window.

Type the code in the Module window.

Here, we have declared ShtName and ShtSearch as variables. ShtName denotes the sheet name and ShtSearch searches the sheet name. We have used the On Error Resume Next statement to run the next statements instantly after the statement that generated the error and the On Error GoTo 0 statement to disable any enabled error.

Press Ctrl + S to save the code.
Then, close the Visual Basic window.
Now, select Macros from the Developer tab. The Macro window will appear.

Now, select the code and Run it from the Macro window.

After running the code, a message box will ask for the sheet name.
Write the sheet name and click OK to proceed.

Finally, another message box will display the search results like the picture below.

Read More: How to Select Sheet by Variable Name with VBA in Excel

2. Search Sheet Name and Select it with VBA in Excel

In this example, we will search the sheet name and select it with Excel VBA. Here, we will not show the result in a message box. Rather, we will select the searched sheet. Let’s pay attention to the steps below.

To begin with, select Visual Basic from the Developer tab. This will open the Visual Basic window.

Search Sheet Name and Select it in Excel with VBA

Click on Module from the Insert tab. The Module window will appear.

After that, type the code in the Module window.

Here, we have created the ShtSearch function to check the existence of the searched sheet in an open workbook. We have used the ShtName = vbNullString command to show Cancelled! if we close the message box.

Next, press Ctrl + S to save the code. Then, close the Visual Basic window.
Go to the Developer tab and select Macros to open the Macro window.

Then, select the desired macro and Run it from the Macro window.

After running the code, you have to write the sheet name in the message box and click OK .

Finally, if it finds the sheet in the open workbook, it will select the sheet.

Otherwise, it will display the Sheet name could not be found! message.

3. Excel VBA to Search Sheet Name in Closed Workbook and Display Search Result

In the last example, we will search for a sheet name in a closed workbook with VBA. Here, we will use a workbook that contains a sheet named Insert Tab . We will search for this sheet name and display the result in a message box.

Let’s observe the steps below to learn more about this example.

Go to the Developer tab and select Visual Basic . This will open the Visual Basic window.

Click on the Insert tab. Then select Module from the drop-down menu to open the Module window.

Then, type the VBA code in the Module window.

Here, we have used the Workbooks.Open() command to open the closed workbook. Workbooks.Open (“D:\Excel Closed Workbook\Hyperlink PDF files.xlsx”) denotes the address location of the closed sheet. It means the VBA code will have to open the file located on the D drive inside the Excel Closed Workbook file and the name of the file is Hyperlink PDF files . We will then have to write the extension of the file. As it is an Excel workbook, we have written .xlsx .

Press Ctrl + S to save the code and close the Visual Basic window.
Now, go to the Developer tab and select Macros . It will open the Macro window.

Select the desired code from the Macro window and Run it.

After that, a message box will appear. Type the name of the sheet you want to search in the closed workbook and click OK .

Finally, you will see a message like the below picture if the searched name exists in the closed workbook.

Read More: How to Rename Sheet with VBA in Excel

Things to Remember

There are certain things we need to remember to search the sheet name with VBA in Excel.

In the above methods, we need to write the sheet name fully in the message box. For example, if you write Display instead of Display Result , it will show Sheet ‘Display’ could not be found!
You can press Alt + F11 to open the Visual Basic window.
Again, you can press the F5 key to run the code instead of running it from the Macro window.

Download Practice Book

We have demonstrated 3 easy examples to search a sheet name in Excel with VBA. I hope these examples will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

What is ExcelDemy?

Tags: VBA Sheet Name

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

Working with Worksheets using Excel VBA (Explained with Examples)

Apart from cells and ranges , working with worksheets is another area you should know about using VBA efficiently in Excel.

Just like any object in VBA, worksheets have different properties and methods associated with them that you can use while automating your work with VBA in Excel.

In this tutorial, I will cover ‘Worksheets’ in detail and also show you some practical examples.

So let’s get started.

If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training .

This Tutorial Covers:

Difference between Worksheets and Sheets in VBA

In VBA, you have two collections that can be a bit confusing at times.

In a workbook, you can have worksheets as well as chart sheets. The example below has three worksheets and one chart sheet.

In Excel VBA:

The ‘Worksheets’ collection refers to the collection of all the worksheet objects in a workbook. In the above example, the Worksheets collection would consist of three worksheets.
The ‘Sheets’ collection would refer to all the worksheets as well as chart sheets in the workbook. In the above example, it would have four elements – 3 Worksheets + 1 Chart sheet.

If you have a workbook that only has worksheets and no chart sheets, then ‘Worksheets’ and ‘Sheets’ collection is the same.

But when you have one or more chart sheets, the ‘Sheets’ collection would be bigger than the ‘Worksheets’ collection

Sheets = Worksheets + Chart Sheets

Worksheets Collection Vs Sheets Collection

Now with this distinction, I recommend being as specific as possible when writing a VBA code.

So, if you have to refer to worksheets only, use the ‘Worksheets’ collection, and if you have to refer to all sheets (including chart sheets), the use the ‘Sheets’ collection.

In this tutorial, I will be using the ‘Worksheets’ collection only.

Referencing a Worksheet in VBA

There are many different ways you can use to refer to a worksheet in VBA.

Understanding how to refer to worksheets would help you write better code, especially when you’re using loops in your VBA code.

Using the Worksheet Name

The easiest way to refer to a worksheet is to use its name.

For example, suppose you have a workbook with three worksheets – Sheet 1, Sheet 2, Sheet 3.

And you want to activate Sheet 2.

The above code asks VBA to refer to Sheet2 in the Worksheets collection and activate it.

Since we are using the exact sheet name, you can also use the Sheets collection here. So the below code would also do that same thing.

Using the Index Number

While using the sheet name is an easy way to refer to a worksheet, sometimes, you may not know the exact name of the worksheet.

For example, if you’re using a VBA code to add a new worksheet to the workbook, and you don’t know how many worksheets are already there, you would not know the name of the new worksheet.

In this case, you can use the index number of the worksheets.

Suppose you have the following sheets in a workbook:

The below code would activate Sheet2:

Note that we have used index number 2 in Worksheets(2) . This would refer to the second object in the collection of the worksheets.

Now, what happens when you use 3 as the index number?

It will select Sheet3.

If you’re wondering why it selected Sheet3, as it’s clearly the fourth object.

This happens because a chart sheet is not a part of the worksheets collection.

Index Numbers in Worksheets Collection in VBA

On the contrary, if you’re using Sheets, Sheets(1) would refer to Sheets1, Sheets(2) would refer to Sheet2, Sheets(3) would refer to Chart1 and Sheets(4) would refer to Sheet3.

This technique of using index number is useful when you want to loop through all the worksheets in a workbook. You can count the number of worksheets and then loop through these using this count (we will see how to do this later in this tutorial).

Note: The index number goes from left to right. So if you shift Sheet2 to the left of Sheet1, then Worksheets(1) would refer to Sheet2.

Using the Worksheet Code Name

One of the drawbacks of using the sheet name (as we saw in the section above) is that a user can change it.

And if the sheet name has been changed, your code wouldn’t work until you change the name of the worksheet in the VBA code as well.

To tackle this problem, you can use the code name of the worksheet (instead of the regular name that we have been using so far).

A code name can be assigned in the VB Editor and doesn’t change when you change the name of the sheet from the worksheet area.

To give your worksheet a code name, follow the below steps:

Click the Developer tab.

Visual Basic button in the Developer tab

Click the View option in the menu and click on Project Window. This will make the Properties pane visible. If the Properties pane is already visible, skip this step.
Click on the sheet name in the project explorer that you want to rename.

The above steps would change the name of your Worksheet in the VBA backend.

In the Excel worksheet view, you can name the worksheet whatever you want, but in the backend, it will respond to both the names – the sheet name and the code name.

Worksheets Object in Excel VBA - Code name vs sheet name

In the above image, the sheet name is ‘SheetName’ and the code name is ‘CodeName’. Even if you change the sheet name on the worksheet, the code name still remains the same.

Now, you can use either the Worksheets collection to refer to the worksheet or use the codename.

For example, both the line will activate the worksheet.

The difference in these two is that if you change the name of the worksheet, the first one wouldn’t work. But the second line would continue to work even with the changed name. The second line (using the CodeName) is also shorter and easier to use.

Referring to a Worksheet in a Different Workbook

If you want to refer to a worksheet in a different workbook, that workbook needs to be open while the code runs, and you need to specify the name of the workbook and the worksheet that you want to refer to.

For example, if you have a workbook with the name Examples and you want to activate Sheet1 in the Example workbook, you need to use the below code:

Note that if the workbook has been saved, you need to use the file name along with the extension. If you’re not sure what name to use, take help from Project Explorer.

Worksheets Object in Excel VBA - file name in project explorer

In case the workbook has not been saved, you don’t need to use the file extension.

Adding a Worksheet

The below code would add a worksheet (as the first worksheet – i.e., as the leftmost sheet in the sheet tab).

It takes the default name Sheet2 (or any other number based on how many sheets are already there).

If you want a worksheet to be added before a specific worksheet (say Sheet2), then you can use the below code.

The above code tells VBA to add a sheet and then uses the ‘Before’ statement to specify the worksheet before which the new worksheet should to be inserted.

Similarly, you can also add a sheet after a worksheet (say Sheet2), using the below code:

If you want the new sheet to be added to the end of the sheets, you need to first know how many sheets are there. The following code first counts the number of sheets , and the adds the new sheet after the last sheet (to which we refer using the index number).

Deleting a Worksheet

The below code would delete the active sheet from the workbook.

The above code would show a warning prompt before deleting the worksheet .

Worksheets Object in Excel VBA - warning prompt

If you don’t want to see the warning prompt, use the below code:

When Application.DisplayAlerts is set to False, it will not show you the warning prompt. If you use it, remember to set it back to True at the end of the code.

Remember that you can’t undo this delete, so use the above code when you’re absolutely sure.

If you want to delete a specific sheet, you can do that using the following code:

You can also use the code name of the sheet to delete it.

Renaming the Worksheets

You can modify the name property of the Worksheet to change its name .

The following code will change the name of Sheet1 to ‘Summary’.

You can combine this with the adding sheet method to have a set of sheets with specific names.

For example, if you want to insert four sheets with the name 2018 Q1, 2018 Q2, 2018 Q3, and 2018 Q4, you can use the below code.

In the above code, we first count the number of sheets and then use a For Next loop to insert new sheets at the end. As the sheet is added, the code also renames it.

Assigning Worksheet Object to a Variable

When working with worksheets, you can assign a worksheet to an object variable, and then use the variable instead of the worksheet references.

For example, if you want to add a year prefix to all the worksheets, instead of counting the sheets and the running the loop that many numbers of times, you can use the object variable.

Here is the code that will add 2018 as a prefix to all the worksheet’s names.

The above code declares a variable Ws as the worksheet type (using the line ‘Dim Ws As Worksheet’).

Now, we don’t need to count the number of sheets to loop through these. Instead, we can use ‘For each Ws in Worksheets’ loop. This will allow us to go through all the sheets in the worksheets collection. It doesn’t matter whether there are 2 sheets or 20 sheets.

While the above code allows us to loop through all the sheets, you can also assign a specific sheet to a variable.

In the below code, we assign the variable Ws to Sheet2 and use it to access all of Sheet2’s properties.

Once you set a worksheet reference to an object variable (using the SET statement), that object can be used instead of the worksheet reference. This can be helpful when you have a long complicated code and you want to change the reference. Instead of making the change everywhere, you can simply make the change in the SET statement.

Note that the code declares the Ws object as the Worksheet type variable (using the line Dim Ws as Worksheet).

Hide Worksheets Using VBA (Hidden + Very Hidden)

Hiding and Unhiding worksheets in Excel is a straightforward task.

You can hide a worksheet and the user would not see it when he/she opens the workbook. However, they can easily unhide the worksheet by right-clicking on any sheet tab.

Worksheets Object in Excel VBA - unhide right-click macro

But what if you don’t want them to be able to unhide the worksheet(s).

You can do this using VBA.

The code below would hide all the worksheets in the workbook (except the active sheet), such that you can not unhide it by right-clicking on the sheet name.

In the above code, the Ws.Visible property is changed to xlSheetVeryHidden .

When the Visible property is set to xlSheetVisible, the sheet is visible in the worksheet area (as worksheet tabs).
When the Visible property is set to xlSheetHidden, the sheet is hidden but the user can unhide it by right-clicking on any sheet tab.
When the Visible property is set to xlSheetVeryHidden, the sheet is hidden and cannot be unhidden from worksheet area. You need to use a VBA code or the properties window to unhide it.

If you want to simply hide sheets, that can be unhidden easily, use the below code:

The below code would unhide all the worksheets (both hidden and very hidden).

Hide Sheets Based on the Text in it

Suppose you have multiple sheets with the names of different departments or years, and you want to hide all the sheets except the ones that have the year 2018 in them.

You can do this using a VBA INSTR function .

The code below would hide all the sheets except the ones with the text 2018 in them.

In the above code, the INSTR function returns the position of the character where it finds the matching string. If it doesn’t find the matching string, it returns 0.

The above code checks whether the name has the text 2018 in it. If it does, nothing happens, else the worksheet is hidden.

You can take this a step further by having the text in a cell and using that cell in the code.

This will allow you to have a value in the cell, and then when you run the macro, all the sheets, except the one with the matching text in it, will remain visible (along with the sheets where you’re entering the value in the cell).

Sorting the Worksheets in an Alphabetical Order

Using VBA, you can quickly sort the worksheets based on their names.

For example, if you have a workbook that has sheets for different departments or years, then you can use the below code to quickly sort these sheets in an ascending order.

Note that this code works well with text names and in most of the cases with years and numbers too. But it can give you the wrong results in case you have the sheet names as 1,2,11. It will sort and give you the sequence 1, 11, 2. This is because it does the comparison as text and considers 2 bigger than 11.

Protect/Unprotect All the Sheets at One Go

If you have a lot of worksheets in a workbook and you want to protect all the sheets, you can use the VBA code below.

It allows you to specify the password within the code. You will need this password to unprotect the worksheet.

The following code would unprotect all the sheets in one go.

Creating a Table of Contents of All Worksheets (with Hyperlinks)

If you have a set of worksheets in the workbook and you want to quickly insert a summary sheet which has the links to all the sheets, you can use the below code.

The above code inserts a new worksheet and names it Index.

It then loops through all the worksheets and creates a hyperlink for all the worksheets in the Index sheet.

Where to Put the VBA Code

Wondering where the VBA code goes in your Excel workbook?

Excel has a VBA backend called the VBA editor. You need to copy and paste the code into the VB Editor module code window.

Here are the steps to do this:

IF Then Else in Excel VBA - Developer Tab in ribbon

In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. If you don’t see the Project Explorer go to the View tab and click on Project Explorer.

You May Also Like the Following Excel VBA Tutorials:

Working with Workbooks using VBA .
Using IF Then Else Statements in VBA .
For Next Loop in VBA .
Creating a User-Defined Function in Excel .
How to Record a Macro in Excel .
How to Run a Macro in Excel .
Excel VBA Events – An Easy (and Complete) Guide .
How to Create an Add-in in Excel .
How to Save and Reuse Macro using Excel Personal Macro Workbook .
Using Active Cell in VBA in Excel (Examples)
How to Open Excel Files Using VBA (Examples)

FREE EXCEL BOOK

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

3 thoughts on “Working with Worksheets using Excel VBA (Explained with Examples)”

How do you refer to the sheet code name of a different workbook? in particular, I would like to set a work book as a variable (set wb = workbook) and then refer to this work book’s sheets using their code names in case individuals decide to change the sheet name.

Hi I’ve created just one worksheet where a number of the columns have drop down lists. I wanted to able to have multiple selection non repetitive on these so I adapted the VBA code as indicated in one of your earlier tutorials. This only worked for a limited number of rows/ columns though and I need it to apply to the whole worksheet. Is there a code that satisfies this??? Is it the code above??? Where do I insert it in existing code?? Your help would be really appreciated

Making the same change to multiple worksheets or say i want to have one master worksheet and the changes i make in master sheet will automatically implement in all other (NOTE : without selecting all worksheets using Ctrl Key)