Get the Name of a Worksheet in Macros VBA in Excel

Add to Favorites

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:

shtName = ActiveSheet.Name

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:

shtName = Worksheets(2).Name

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!

Downloadable Files: Excel File

Similar Content on TeachExcel
Activate or Navigate to a Worksheet using Macros VBA in Excel
Tutorial: Make a particular worksheet visible using a macro in Excel. This is called activating a wo...
Change the Name of a Chart in Excel
Tutorial: How to change the name of a chart in Excel. This allows you to use a more intuitive char...
Output the File Path to and Name of a Workbook in Excel - UDF
Macro: Free Excel UDF (user defined function) that displays the full file path and name of an Exc...
Remove Gridlines from Only Part of a Worksheet in Excel
Tutorial: How to remove gridlines from a part of a worksheet in Excel instead of the entire workshe...
Get Day Name from a Date in Excel
Tutorial: How to get the name of a day from a date in Excel. This returns, for example, "Tuesday" fo...
Get the Number of Workdays Between Two Dates in Excel
Tutorial: How to calculate the total number of working days between two dates in Excel.  This allows...