VBA Name Worksheet

What is Excel VBA Name Worksheet?

The Name property is used to get or set the name of an object. When you refer to a worksheet in VBA, you can use the Name property to retrieve or change the name of that worksheet.

You can see how to use the “Name” property to change the name of the Worksheet with VBA with a simple example.

VBA Name Worksheet - Definition Example - 1

Here, the active sheet or the current sheet in the Excel Workbook is “Sheet1.” You can either call it by name or use the ActiveSheet keyword, as shown below.

VBA Name Worksheet - Definition Example - 2

A subroutine is created to change the currently used sheet into any string form given. The ActiveSheet, which is “Sheet1”, is changed to “Example_name” instead once the code is run. In this way, the VBA Name worksheet function can be utilized effortlessly.

VBA Name Worksheet - Definition Example - 3
Key Takeaways
  • In VBA (Visual Basic for Applications), the Name property is used to get or set the name of an object.
  • To rename a worksheet in VBA, you use the Name property of a Worksheet object.
  • The Name property is applied to a specific worksheet, and you can use it to change the name of that worksheet. Worksheet names in VBA must be unique within a workbook. Example: Worksheets("Sheet1").Name = "NewSheetName" renames "Sheet1" to "NewSheetName."
  • Ensure uniqueness and handle errors when renaming sheets to avoid issues.

How to Use Excl VBA Name Worksheet?

To learn how to name Worksheets in VBA follow the steps below.

  1. Once you open the Excel Workbook, in the title bar, choose the “Developer” icon and click on it.


    How to use Excel VBA Name WorkSheet - Step 1a

    After you click the Developer icon, select “Visual Basic.” It opens the VBA window where you can start coding.



    In the VBA Editor, in the title bar, click the “Insert” button and select the “Module” option.



    Now, you are ready to start coding in Excel VBA. The name of the new Worksheet is currently “Sheet1.”

  2. Name the subroutine you are using to apply the VBA name worksheet with variable.


    How to use Excel VBA Name WorkSheet- Step 2

  3. You can index worksheets as shown below. Assign the worksheet’s name in the second index to a variable.


    How to use Excel VBA Name WorkSheet- Step 3

  4. Print the variable using the Debug.Print function.


    How to use Excel VBA Name WorkSheet- Step 4a

    Output

  5. Assign the same variable with the name of the new Worksheet.


    How to use Excel VBA Name WorkSheet- Step 5

  6. Change the worksheet’s name by assigning it to the value of the variable initialized previously.


    How to use Excel VBA Name WorkSheet- Step 6

  7. Print the new name using a MsgBox function. Every worksheet in Excel has a worksheet name, and there’s the code name of the worksheet, which remains the same regardless of whatever name change you make.


    How to use Excel VBA Name WorkSheet- Step 7a



    It means we can call the name of the Worksheet by calling its name with the code name “Sheet2,” which is the value printed in the MsgBox function.
     
    Code:

    Sub Change_name_with_variable()
    SheetName = Worksheets(2).name
    Debug.Print SheetName
    SheetName = “NewWorksheet”
    Worksheets(2).name = SheetName
    MsgBox “Name has been changed to ” & Sheet2.name
    End Sub

  8. Click the “Run” icon on the activity bar in the Excel VBA Module to run the program. It prints an output with the different date formats.


    How to use Excel VBA Name WorkSheet- Step 8a

    If you haven’t placed your cursor on any of the subroutines, you will get the list to run the macros you want.



    Double-click on the subroutine to run it. The previous name of the Sheet is printed in the Immediate tab.



    Now, you will get a MsgBox prompt.



    Now, you can view the changed Worksheet name.



    To learn further, go through some more examples.

Examples to Name Worksheet using VBA

You can view the different ways to name worksheets in Excel VBA by going through some examples below.

Example #1

In this example, you can copy worksheets in VBA and rename them subsequently. You can use the VBA name worksheet today’s date to store the current day’s quota in a worksheet. Follow the steps below.

  • Step 1: Name the subroutine to change the copied worksheet’s name.
Example 1 - Step 1

  • Step 2: Using the VBA Copy function, copy the worksheet of your choice with its name mentioned and set it near the end using the “After” keyword. ‘Sheets.Count’ returns the number of Worksheets; it will place the copied worksheet at the end of the worksheet index.
Example 1 - Step 2

The copied worksheet will have the same name as the sheet that it is copied from, except since it is a copy and to avoid naming conflicts, Excel adds a numerical value after the sheet name as seen below.

Example 1 - Step 2b
  • Step 3: Print the name of the copied worksheet using the MsgBox function.
Example 1 - Step 3
  • Step 4: To prevent an error interrupting the running of the subroutine, you can use Resume Next along with error handling.
Example 1 - Step 4
  • Step 5: Once the worksheet is copied, the copied worksheet becomes the active sheet. Hence, you can change its name to CopiedSheets. by using the ActiveSheet keyword as shown.
Example 1 - Step 5
  • Step 6: If errors persist, you can disable error handling by returning to its default value once any error comes through. This is to ensure that the code runs continuously.
Example 1 - Step 6
  • Step 7: Print the worksheet name of the sheet that was copied.
Example 1 - Step 7
  • Step 8: Now, you can assign the name of the current worksheet to the day’s date using the VBA name worksheet today’s date.
Example 1 - Step 8

The Date function in Excel VBA returns the current date or today’s date that is set in your Excel workbook.

  • Step 9: With the MsgBox function, print the changed name of the worksheet.
Example 1 - Step 9

Code:

Sub Change_Copied_Sheet()
Worksheets(“NewWorksheet”).Copy After:=Sheets(Sheets.Count)
MsgBox ActiveSheet.name, vbInformation
On Error Resume Next
ActiveSheet.name = “CopiedSheets”
On Error GoTo 0
MsgBox “Changed to ” & ActiveSheet.name
ActiveSheet.name = Date
MsgBox “Changed to ” & ActiveSheet.name
End Sub

  • Step 10: Press “F5” to run the subroutine. First, the name of the Worksheet that is copied is printed in a Message box.
Example 1 - Step 10a
Example 1 - Step 10b

Then, the name of the worksheet changes. The new name is printed in a message box.

Example 1 - Step 10c
Example 1 - Step 10d

It changes afterwards because the code implements the VBA Name Worksheet today’s date.

Example 1 - Step 10e
Example 1 - Step 10f

Example #2

Suppose you have a list of worksheets in your Excel workbook, and you want to change them all to different names. You can enter the name that you want to change it to in any cell and run all the worksheets through a FOR-loop. You can use the VBA Name Worksheet in a FOR loop.

Excel - VBA Name Worksheet in Excel - Example 2

For example, these are the worksheets you have in your workbook. You have a name selected in cell “A1” in each worksheet as shown.

Example 2 - 1
Example 2 - 2
Example 2 - 3
Example 2 - 4

You can now start building the code to change the names of the Worksheets by following the steps below.

  • Step 1: Initialize a subroutine to perform the VBA Name Worksheet from cell value.
Example 2 - Step 1
  • Step 2: Declare a worksheet variable, w in this case. It will be used as an iterative variable in a FOR-loop to run through every sheet in the workbook.
Example 2 - Step 2
  • Step 3: Define a string variable in VBA to store the cell value to later perform the VBA Name Worksheet with variable.
Example 2 - Step 3
  • Step 4: Start a FOR-loop with the Worksheet variable defined to run through all the sheets of the Workbook using the “ThisWorkbook.Worksheets” function to select all the worksheets.
Example 2 - Step 4
  • Step 5: Initialize the string variable with the value of cell “A1” in the FOR-loop.
Example 2 - Step 5

This means that in each worksheet, the cell value of “A1” is taken in and used for changing the name of the worksheets.

  • Step 6: Define an If-Else statement to check if the values aren’t empty to prevent any errors.
Example 2 - Step 6

If the cell value isn’t empty, using the Name property, change the name of the worksheet by equating it to the string value. Then, end the conditional statement.

  • Step 7: Continue the FOR-loop for subsequent worksheets.
Example 2 - Step 7

Code:

Sub Rename_Sheets_with_cell_values()
Dim w As Worksheet
Dim val As String
For Each w In ThisWorkbook.Worksheets
val = w.Range(“A1”).Value
If val <> “” Then
w.name = val
End If
Next w
End Sub

  • Step 8: Run the subroutine by clicking on the green button on the VBA Toolbar.
Excel - VBA Name Worksheet in Excel - Example 2 - Step 8a

After running the code, you can see that the code name of the sheets remains unchanged.

Excel - VBA Name Worksheet in Excel - Example 2 - Step 8b

Go to the worksheet to view the changes.

Excel - VBA Name Worksheet in Excel - Example 2 - Step 8c

Important Things to Note

  • Ensure that the new worksheet name is unique within the workbook to avoid conflicts.
  • Validate that the new name doesn’t contain characters not allowed in sheet names.
  • Don’t set worksheet names to blank values. Ensure that the new name is not an empty string.
  • Neglecting error handling can lead to runtime errors, especially if the chosen name already exists or contains invalid characters.

Frequently Asked Questions (FAQs)

1. Can I use a variable to dynamically name a worksheet in VBA?

Yes, you can use a variable to dynamically name a Worksheet in VBA. Declare the variable and assign it to the value you want your sheet to be changed to.
See the example below:
 
SheetName = "NewWorksheet"
Worksheets(2).name = SheetName

 
This code snippet utilizes the variable to store the string value and assign it to the Worksheet afterward.

2. How do I check if a worksheet name already exists before renaming it in VBA?

You can check if a worksheet name already exists before renaming it in VBA using this line of code:
 
If WorksheetExists(newName) Then
 
After this, write alternative statements. In this way, you can check if a worksheet name already exists.

3. Can I use a cell value as the new worksheet name in VBA?

Yes, you can use the VBA Name worksheet from cell value to rename worksheet names in VBA. You can either use variables or assign them directly.
 
Suppose the value of cell “A1” is “TEST”. The name changes can be done in 2 ways.
 
With variables:
Sub eg()
    v = Worksheets(1).Range("A1").Value
    Worksheets(1).name = v
End Sub

 
Without variables:
Sub eg2()
    Worksheets(2).name = Worksheets(2).Range("A1").Value
End Sub

 
With these two ways, you can rename a worksheet in VBA using cell values.

4. Is it possible to rename multiple worksheets at once in VBA?

Yes, you can rename multiple worksheets at once using FOR-loops with the Worksheet variable running through ThisWorkbook.Sheets.

For Example:
For Each w In ThisWorkbook.Worksheets
        val = w.Range("A1").Value
        w.name = val

 
In this way, you can rename multiple worksheets at once.

Download Template

This article must be helpful to understand the VBA Name Worksheet, with it features and examples. You can download the template here to use it instantly.

This has been a guide to VBA Name Worksheet. Here we learn how to name Excel Worksheets using VBA coding, with examples & points to remember. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *