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.
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.
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.
Table of contents
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.
- Once you open the Excel Workbook, in the title bar, choose the “Developer” icon and click on it.
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.” - Name the subroutine you are using to apply the VBA name worksheet with variable.
- You can index worksheets as shown below. Assign the worksheet’s name in the second index to a variable.
- Print the variable using the Debug.Print function.
Output - Assign the same variable with the name of the new Worksheet.
- Change the worksheet’s name by assigning it to the value of the variable initialized previously.
- 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.
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 - 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.
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.
- 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.
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.
- Step 3: Print the name of the copied worksheet using the MsgBox function.
- Step 4: To prevent an error interrupting the running of the subroutine, you can use Resume Next along with error handling.
- 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.
- 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.
- Step 7: Print the worksheet name of the sheet that was copied.
- 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.
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.
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.
Then, the name of the worksheet changes. The new name is printed in a message box.
It changes afterwards because the code implements the VBA Name Worksheet today’s date.
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.
For example, these are the worksheets you have in your workbook. You have a name selected in cell “A1” in each worksheet as shown.
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.
- 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.
- Step 3: Define a string variable in VBA to store the cell value to later perform the VBA Name Worksheet with variable.
- 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.
- Step 5: Initialize the string variable with the value of cell “A1” in the FOR-loop.
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.
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.
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.
After running the code, you can see that the code name of the sheets remains unchanged.
Go to the worksheet to view the changes.
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)
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.
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.
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.
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.
Recommended Articles
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 –
Leave a Reply