VBA Get Sheet Name / Rename Sheet

Associated Files Download Links

This tutorial will cover interacting with Sheet names in VBA.

Get Sheet Name

Sheet names are stored in the Name property of the Sheets or Worksheets object.  The Sheet Name is the “tab” name that’s visible at the bottom of Excel:

vba sheet tab name

Get ActiveSheet Name

This will display the ActiveSheet name in a message box:

Get Sheet Name by index Number

This will display the first worksheet name in a message box:

This will display the name of the last worksheet in the workbook:

Get Sheet Name by Code Name

In the VBA Editor, there is an option to change the “code name” of a Sheet. The code name is not visible to the Excel user and can only be seen in the VBA Editor:

vba sheet code name

In VBA, when working with Sheets, you can reference the usual Tab name:

or the VBA code name:

Referencing the code name is desirable in case the Sheet tab name ever changes. If you allow you Excel user access to changing sheet names you should reference the code name in your VBA code so that a Sheet tab name mismatch doesn’t cause an error. Sheet code names are discussed in more detail here.

To get the Sheet name using the VBA Code name, do the following:

 

Rename Sheet

You can rename Sheets by adjusting the name property of the Sheets or Worksheets object.

Rename ActiveSheet

Rename Sheet by Name

Rename Sheet by Sheet Index Number

Here we use 1 to rename the first Sheet in the Workbook.

Rename Sheet by Code Name

This code will rename a sheet using it’s VBA code name (discussed above):

 

Check if Sheet Name Exists

We created a function to test if a Sheet with a particular name already exists.


The function will return TRUE if the Sheet exists, or FALSE if it does not.

Use the function like so:

 

Copy Sheet and Rename

This example is from our article on Copying Sheets.

After copying and pasting a Sheet, the newly created sheet becomes the ActiveSheet. So to rename a copied Sheet, simply use ActiveSheet.Name:

Note: We added error handling to avoid errors if the Sheet name already exists.

You may also like some of this related content...

Ads