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

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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 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 allow...
Highlight, Sort, and Group the Top and Bottom Performers in a List in Excel
Tutorial: How to highlight the rows of the top and bottom performers in a list of data. This allows...