In Excel VBA, you can rename a sheet by setting its ‘Worksheet.Name’ property to the desired text string value.
In this tutorial, I will present to you several examples of Excel VBA code that you can customize and use to rename a worksheet or worksheets in a workbook.
VBA to Rename a Single Sheet
In this section, I present to you examples of Excel VBA code that you can use to rename one sheet in a workbook.
VBA to Rename the Active Sheet
Suppose you want to rename the active sheet in the current workbook as ‘Annual Budget.’ You can use the VBA code below to accomplish the task:
Sub RenameActiveSheet()
ActiveSheet.Name = "Annual Budget"
End SubVBA to Rename a Sheet (Identified by Its Index Number)
Excel assigns an index number to each sheet you add to a workbook. For example, Excel assigns index 1 to the first sheet you add to a workbook, index 2 to the second sheet, and so on.
You can use the sheet’s index number to identify the sheet you want to rename.
Suppose you want to rename the second sheet you added to the current workbook as ‘Annual Sales.’ You can use the following code to do the task:
Sub RenameSheetByIndex()
ThisWorkbook.Sheets(2).Name = "Annual Sales"
End SubVBA to Rename a Sheet (Identified by Its Current Name)
Suppose you want to rename the sheet ‘Schools’ in the current workbook as ‘Colleges.’ You can use the subroutine below to achieve that:
Sub RenameSheetByCurrentName()
ThisWorkbook.Sheets("Schools").Name = "Colleges"
End SubVBA to Rename a Sheet to a Cell Value
You can rename a sheet to a cell’s value in VBA. Suppose the value in cell A1 of the active sheet is ‘Progress Report.’
You can rename the active sheet as the value in that cell using the subroutine below:
Sub RenameSheetToCellValue()
ActiveSheet.Name = Range("A1").Value
End SubNote: If cell A1 is empty, the code will trigger the error message ‘Run-time error 1004:application-defined or object-defined error’ shown below:
If the value in cell A1 has any of the disallowed special characters mentioned in the section ‘Guidelines for Renaming Sheets,’ you will get the error message ‘Run-time error 1004: You typed an invalid name for a sheet or chart’ shown below:
VBA to Rename a Sheet in Another Open Workbook
Suppose you want to rename Sheet1 in another open workbook, ‘ Experiments.xlsx,’ as ‘Instructions.’ You can use the VBA code below to accomplish the task:
Sub RenameSheetAnotherWorkbook()
Workbooks("Experiments.xlsx").Sheets("Sheet1").Name = "Instructions"
End SubVBA to Rename Sheet as the Current Date/Time
The code below renames the active sheet as the current date:
Sub RenameSheetByDate()
ActiveSheet.Name = Format(Now(), "dd-mm-yyyy")
End SubThe code uses the ‘Now’ function to get the current date and time and then the ‘Format’ function to apply the dd-mm-yyyy format to the date.
The code then sets the ‘ActiveSheet.Name’ property to the formatted date string, thus renaming the sheet as the current date.
If you run the code and the workbook already has a sheet named the current date, you will get the error message ‘Run-time error 1004: That name is already taken. Try a different one’ shown below:
Each sheet in a workbook must have a unique name. You can add the time component to the date format to make each sheet renamed as the current date unique.
You can use the subroutine below to rename the active sheet as the current date and time:
Sub RenameSheetByDateTime()
ActiveSheet.Name = Format(Now(), "dd-mm-yyyy_hh_mm_ss")
End SubNotice that I have used underscores instead of full colons in the time component of the date and time format because Excel does not allow full colons in sheet names.
VBA to Rename Sheet As Workbook Name
Sometimes, you may need to rename a sheet as the workbook name. Suppose the current workbook is named ‘Performance.xlsx.’ You can rename the active sheet as the current workbook name using the VBA code below:
Sub RenameSheetAsWorkbookName()
Dim wbName As String
wbName = Mid(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\") + 1)
wbName = Left(wbName, InStrRev(wbName, ".") - 1)
ActiveSheet.Name = wbName
End SubThe code uses the ‘Mid,’ ‘InStrRev,’ and ‘Left’ functions to extract the workbook name from the file’s full path and remove its file extension.
It then assigns the resultant string to the ‘wbName’ variable and finally renames the active sheet as the value in the ‘wbName’ variable.
Note: This code only works if you have saved the workbook. If you run it in a new workbook that you have not yet saved, you will get the error message ‘Run-time error 5: Invalid procedure call or argument’ shown below:
VBA to Add a Prefix/Suffix to a Sheet Name
Suppose you want to add the prefix ‘Annual_’ before the sheet name of the ‘Budget’ worksheet in the current workbook. The following code will do it for you:
Sub PrefixSheet()
Dim ws As Worksheet
Dim preFix As String
Set ws = ThisWorkbook.Sheets("Budget")
preFix = "Annual_"
ws.Name = preFix & ws.Name
End SubIf you want to append a suffix to the end of the worksheet name, you can modify the code as follows:
Sub SuffixSheet()
Dim ws As Worksheet
Dim Suffix As String
Set ws = ThisWorkbook.Sheets("Colleges")
Suffix = "_Annual"
ws.Name = ws.Name & Suffix
End SubVBA to Check If Sheet Exists Before Renaming
If you attempt to rename a non-existent sheet using VBA, you will get the error message ‘Run-time error 9: Subscript out of range’ shown below:
So that you don’t get error 9, it is recommended that the code first check whether the sheet exists. If it does, the code then renames the sheet or graciously exits if it doesn’t.
The code below checks whether the sheet ‘Evaluation’ exists in the current workbook.
If it does, it renames it ‘Appraisal Report’ and displays a message box indicating that the sheet has been renamed. If it does not, the code displays a message box stating it does not exist.
Sub CheckSheetExistsRename()
Dim wb As Workbook
Dim ws As Worksheet
Dim sheetExists As Boolean
Set wb = ThisWorkbook
sheetExists = False
For Each ws In wb.Sheets
If ws.Name = "Evaluation" Then
sheetExists = True
Exit For
End If
Next ws
If sheetExists Then
wb.Sheets("Evaluation").Name = "Appraisal Report"
MsgBox "Sheet has been renamed."
Else
MsgBox "Sheet does not exist."
End If
End SubAlso read: VBA to Copy Sheet to New Workbook
VBA to Rename Multiple Sheets
In this section, I present to you examples of Excel VBA code that you can use to rename multiple sheets in a workbook.
VBA to Rename All Sheets in the Current Workbook As Cell Values
Suppose you want to rename each sheet in the current workbook as the value in cell A1. You can use the subroutine below to accomplish the task:
Sub RenameSheetsAsCellValues()
Dim ws As Worksheet
Dim cellValue As String
For Each ws In ThisWorkbook.Sheets
cellValue = ws.Range("A1").Value
If cellValue <> "" Then
ws.Name = cellValue
End If
Next ws
End SubThe code uses the ‘For Each Next’ construct to loop through all the sheets in the current workbook and renames each sheet as the value in its cell A1.
The code checks whether cell A1 is empty; if it is, the code leaves the sheet’s name as is.
Note: Before running the code, ensure that the values in cell A1 of each sheet do not contain any disallowed special characters mentioned in the section ‘Guidelines for Renaming Sheets.’ Additionally, ensure that the cells do not contain duplicate values.
VBA to Rename All Sheets in the Current Workbook As Values on a List
With VBA, you can easily rename sheets in a workbook based on values on a list.
Suppose you have the following list on Sheet1 of the current workbook:
You can use the VBA code below to rename the sheets in the workbook as values in the list:
Sub RenameSheetsAsListValues()
Dim ws As Worksheet
Dim i As Long
i = 1
For Each ws In Worksheets
If Range("A1").Offset(i - 1, 0) <> "" Then
ws.Name = ActiveSheet.Range("A1").Offset(i - 1, 0)
End If
i = i + 1
Next ws
End SubWhen you execute the code, it renames all the sheets in the workbooks as values on the list:
The code uses the i counter variable and the ws worksheet variable.
The code uses the ‘For Each Next’ construct to loop through each workbook.
Inside the loop, the code uses the ‘If End If’ construct to check whether the cell at an offset from cell A1 (identified using the counter variable) is empty.
If it is not, the code renames the target worksheet as the value in the cell, increments the counter variable by one, gets the following worksheet, and repeats the process until all the worksheets in the workbook have been renamed.
VBA to Add a Prefix/Suffix to All Sheets in the Current Workbook
Suppose you want to add the prefix ‘Regional_’ before the names of all the sheets in the current workbook. You can use the VBA code below to do the task:
Sub PrefixAllSheets()
Dim ws As Worksheet
Dim preFix As String
preFix = "Regional_"
For Each ws In ThisWorkbook.Worksheets
ws.Name = preFix & ws.Name
Next ws
End SubThe code uses a ‘ws’ Worksheet variable and a ‘preFix’ String variable.
The above code uses the ‘For Each Next’ construct to loop through each sheet, adding the text string ‘Regional_’ in the ‘preFix’ variable before the sheet name.
You can use the code below to append a suffix after the names of all the sheets in the current workbook:
Sub SuffixAllSheets()
Dim ws As Worksheet
Dim Suffix As String
Suffix = "_Regional"
For Each ws In ThisWorkbook.Worksheets
ws.Name = ws.Name & Suffix
Next ws
End SubThe above code uses the ‘For Each Next’ construct to loop through each sheet, appending the text string ‘_Regional’ in the ‘Suffix’ variable after the sheet name.
Also read: Protect Excel Workbook Using VBA
Guidelines for Renaming Sheets in Excel
You must adhere to the following guidelines when renaming sheets:
- The name of a sheet should not exceed 31 characters.
- Excel does not allow the following special in sheet names: / (forward slash), \ (backslash), : (full colon),? (question mark), * (asterisk), [ (left square bracket), or ] (right square bracket).
- A sheet name cannot be an empty string.
- You cannot rename two or more sheets in a workbook with the same name; each sheet must have a unique name.
- A sheet name cannot begin with a space character.
- Sheet names are case-insensitive. For instance, Excel interprets SHEET1, Sheet1, and sheet1 as referring to the same worksheet in a workbook.
In this tutorial, I gave you several Excel VBA code examples that you can modify and use to rename a sheet or sheets in a workbook. I hope you found the tutorial helpful.
Other Excel articles you may also like:
