VBA Rename Sheet

What is Rename Sheet in Excel VBA?

In Excel VBA (Visual Basic for Applications), the “Rename Sheet” refers to a task or operation that programmatically changes the name of a worksheet in an Excel workbook. It allows you to dynamically modify the name of a sheet based on certain conditions or user inputs. To rename a sheet in Excel VBA, you can use the Name property of the Worksheet object. Consider the following example:

This code converts the name of the current worksheet, “Sheet1,” to “RenameExample,” as shown below:

You can modify the code to suit your specific requirements. For instance, you can dynamically use conditions, loops, or input boxes in VBA to determine the new sheet name.

How to Rename Sheet in VBA?

To implement the VBA Rename sheet, we perform the following steps as shown below:

Step 1: Go to the “Developer tab” in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and sub-procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.

Consider an example where you need to perform a VBA Rename sheet for all the worksheets in a workbook.

Step 2: Initialize a sub-procedure to Rename a sheet based on user input.

Step 3: Declare two variables, ‘ws,’ to iterate through all sheets to change their names and ‘newname’ as String to store the new names input by the user.

Step 4: Initialize a FOR loop that runs through the whole workbook with ws as the iterative variable.

Step 5: In the FOR loop, use an Input box function to get names from the user to change each sheet name.

We also check whether the given input is not an empty string since it is not a valid naming convention. After all the sheets are renamed, the FOR loops end.

Code:

Sub RenameSheets()
Dim ws As Worksheet
Dim newName As String
For Each ws In ThisWorkbook.Worksheets
newName = InputBox(“Enter a new name for sheet: ” & ws.Name)
If newName <> “” Then
ws.Name = newName
End If
Next ws
End Sub

Step 6: Run the code by pressing the ‘F5’ button or running the green arrow button present on top of the VBA toolbar. The output is shown below:

Note: It is essential to be cautious when renaming sheets using VBA, especially if you have formulas or references that rely on the sheet names. Ensure any references or formulas are correctly updated after renaming the sheets to avoid errors.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples of Rename Worksheets in Excel VBA

Let us look at some interesting examples of renaming worksheets in Excel VBA.

Example 1 – Change or Rename sheet using VBA Variables

Consider an example where you want to perform a VBA rename sheet with today’s date on “MSExcelSheet1” using the VBA Variables method. The steps to do as such are shown below:

Step 1: Define a sub-procedure that performs VBA Rename Sheet with VBA Variables:

Step 2: Define three variables, ‘ws’ to define the worksheet’s name to be changed, ‘oldName,’ which is the sheet’s current name; and ‘newName,’ which holds the new name of the sheet to be changed to.

Step 3: Assign values for oldName and newName. Here, the VBA Date function is used to perform VBA rename sheet to today’s date.

Here, error handling is done to prevent VBA from showing runtime errors if the Sheet name is not found in the Worksheet names.

Step 4: Assign the ‘ws’ variable to the sheet with the string value of ‘oldName.’ Perform error handling again if the string value present in oldName does not exist.

In this case, VBA rename sheet after the copy has been performed.

Step 5: Initialize VBA IF Else statement to check whether the name has been changed or if the sheet exists; otherwise, print “Sheet not found!”

Code:

Sub RenameSheetUsingVariable()
Dim ws As Worksheet
Dim oldName As String
Dim newName As String
oldName = “MSExcelSheet1”
newName = Date
On Error Resume Next
Set ws = ThisWorkbook.Sheets(oldName)
On Error GoTo 0
If Not ws Is Nothing Then
ws.Name = newName
MsgBox “Sheet renamed successfully!”
Else
MsgBox “Sheet not found!”
End If
End Sub

Step 6: Run the code. The output is as shown below:

Example 2 – Get all the Worksheet Names in a Single Sheet

Consider an example where we must create a new sheet to insert all sheet names present in the workbook in VBA rename sheet.

Step 1: Declare a sub-procedure to create a new worksheet, customize its name, and input all the Sheet names in that sheet.

Step 2: Declare variables ‘ws’ as an iterative variable that passes through all the worksheets, ‘newSheet’ to Rename the new sheet name, ‘sheetNames’ as the variable which holds all the Sheet names as string values, and ‘rowCount’ to print the sheet names iteratively in subsequent rows.

Step 3: Create a new worksheet using the Sheets.Add method and name the new sheet.

The new sheet is added after the last sheet using the ‘After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)’ function.

Step 4: Assign values to rowCount as to where it should start printing all the sheet names.

In this case, VBA rename sheet after the copy has been performed.

Step 5: Initialize a FOR loop that goes through the whole Excel workbook iterating through sheet names and storing them in the sheetNames variable to be printed in the “WorksheetData” sheet.

The rowCount is appended to fill in the other sheet names in subsequent rows.

Step 6: Declare the column title for all the sheet names in cell “A1.”

Code:

Sub ListWorksheetNames()
Dim ws As Worksheet
Dim newSheet As Worksheet
Dim sheetNames As String
Dim rowCount As Long
Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
newSheet.Name = “WorksheetData”
rowCount = 2
For Each ws In ThisWorkbook.Worksheets
sheetNames = ws.Name
newSheet.Cells(rowCount, 1).Value = sheetNames
rowCount = rowCount + 1
Next ws
newSheet.Range(“A1”).Value = “Worksheet Names”
End Sub

Step 7: Print the above code. The output is printed on the worksheet “WorksheetData.”

It is observed that the new worksheet created to store data is also stored in the worksheet.

Example 3 – Set Permanent Name to the Excel Worksheet Using VBA

Consider an example where you want to permanently change the name of a Worksheet, locking the name using the ‘Protect’ mode in Excel VBA.

Step 1: Create a sub-procedure to implement VBA rename sheet based on cell value.

Step 2: Declare variables ‘ws,’ which defines the current worksheet/ worksheet name to be changed, and ‘permanentName’ as the name for the sheet to be permanently renamed.

Step 3: Assign ws to the sheet to be changed.

Step 4: Assign the permanent name of the sheet to the value present in cell “A1” of that worksheet.

Step 5: Replace the sheet name using the VBA Name function.

Step 6: Set the name permanently. Henceforth, the name of the sheet cannot be renamed once the sheet is declared in “Protect” mode.

Code:

Sub SetPermanentSheetName()
Dim ws As Worksheet
Dim permanentName As String
Set ws = ThisWorkbook.Sheets(“RenamedSheet1”)
permanentName = Range(“A1”).Value
ws.Name = permanentName
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Step 7: Run the code. The output will be viewed in the Excel Workbook.

Important Things To Note

• Use error handling: When renaming sheets, it’s advisable to use techniques, such as On Error Resume Next and On Error GoTo, to handle any potential errors or exceptions that may arise during the renaming process.
• Check for existence: Before renaming a sheet, check if the sheet with the specified name exists in the workbook. You can use conditional statements or error handling to validate the existence of the sheet before performing the renaming operation.
• Update references: If the renamed sheet is referenced in formulas, charts, or other places within the workbook, update those references accordingly after renaming the sheet. Failure to update references can lead to errors in your workbook.
• Avoid using reserved characters: When specifying the new sheet’s name, avoid using reserved characters such as slashes (/), backslashes (), question marks (?), asterisks (*), brackets ([]), and colons (:). These characters have special meanings in Excel and can cause errors or unexpected behavior when used in sheet names.
• Don’t use duplicate names: Ensure that another sheet does not already use the new name you assign to a sheet within the same workbook. Duplicate names can lead to conflicts and ambiguity, making it difficult to reference the intended sheets.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Rename Sheet, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Rename Sheet. Here we learn how to rename sheet of an excel workbook using Name property with examples and downloadable template. You may learn more from the following articles –

• Share An Excel Workbook
• VBA Copy Worksheet
• VBA 1004 Error