Get the Name of a Worksheet in Macros VBA in Excel

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:

ActiveSheet.Name

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:

Worksheets(2).Name

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!