Set the sheet’s Name property:
ThisWorkbook.Worksheets("Sheet1").Name = "Sales"
The sheet does not need to be active. Use ActiveSheet.Name = "Sales" for the sheet on screen, or Worksheets(2).Name = "Sales" to rename by tab position.
To take the name from a cell, qualify the cell so it reads the right sheet: ws.Name = ws.Range("A1").Value. If the cell holds a date, format it first, as in ws.Name = Format(ws.Range("A1").Value, "dd-mmm-yyyy"), because a date with slashes is not a valid sheet name.
A sheet name can be up to 31 characters, cannot be blank, cannot contain \ / ? * [ ] :, cannot start or end with an apostrophe, and cannot be History. It must also differ from every other sheet name in the file, ignoring case. Breaking any of these stops the macro with error 1004.
Check that the new name is free before renaming:
Dim sh As Object
On Error Resume Next
Set sh = ThisWorkbook.Sheets("Sales")
On Error GoTo 0
If sh Is Nothing Then ThisWorkbook.Worksheets("Sheet1").Name = "Sales"
Renaming updates formulas that point to the sheet, but not VBA code. A macro that refers to Worksheets("Sheet1") stops with “Subscript out of range” once the tab is renamed. Use the sheet’s code name, such as Sheet1.Range("A1"), in code that has to survive a rename.
When you add a new sheet to a workbook, you have the option to name it. But you can also rename it at any time using the Name property of the worksheet.
In this tutorial, we will look at different ways to rename a sheet or multiple sheets using a VBA code.
Steps to Rename a Sheet using a VBA Code
- First, define a sheet or a worksheet with its name “Sheets(“Sheet1”)” that you want to rename using the worksheet object.
- After that, you need to use (.Name) to access the name property that allows you to rename a sheet with a new name.
- Next, type an equal sign (=) to tell VBA the value you want to use to rename the sheet.
- In the end, type the name that you want to give to the sheet. But make sure to specify the name in double quotation marks, like “mySheet”.

Rename the Active Sheet using a VBA Code
If you want to rename the active sheet, you don’t need to define the sheet name. Instead, you need to use the ActiveSheet object that tells VBA to refer to the sheet that is active right now.
Here’s the code.
Activesheet.Name = "mySheet"

Note: To rename a sheet you don’t need to activate it.
Rename a Sheet using Sheet Number
As you know, every sheet has a number based on its position in the workbook. Let’s say you want to rename the sheet in the fifth position; the code would be:
Sheets(5).Name = "mySheet5"

When you run the above macro, it renames the sheet that is in the fifth position.
Sheets(5) counts all the sheets, including chart sheets. If your workbook has chart sheets and you only want to count worksheets, use Worksheets(5) instead. And if there are fewer than five sheets, you get the “Subscript out of range” error (Error 9).

Check if the Sheet Exists before Renaming
If you try to rename a worksheet that doesn’t exist, VBA will show you the “Subscript out of range” error (Error 9), just like below.

The solution to this problem is the following code that uses FOR EACH, which can loop through all the worksheets to find the sheet that you have defined and then rename that sheet.
Sub check_sheet_rename()
'This line declares a variable called ws to represent a Worksheet
Dim ws As Worksheet
'These lines declare two variables, mySheet and SheetName, to represent strings.
Dim mySheet As String
Dim SheetName As String
'This line prompts the user to enter a new name for the sheet. The input is stored in the SheetName variable.
mySheet = InputBox("enter the name of the sheet that you want to rename.")
SheetName = InputBox("Enter new name for the sheet.")
'This line starts a loop that will iterate through each worksheet in the current workbook.
For Each ws In ThisWorkbook.Worksheets
'This line checks if the name of the current worksheet in the loop matches the name entered by the user.
If mySheet = ws.Name Then
'If the condition in step 6 is met, this line renames the worksheet to the new name entered by the user.
ws.Name = SheetName
'This line ends the If statement.
End If
'This line ends the For Each loop.
Next ws
End Sub
Note that the comparison is case-sensitive, so “sheet1” won’t match “Sheet1”. And if no sheet has that name, the code ends without doing anything, so you may want to add a message for that case.
Rename a Sheet using Value from a Cell or a Range
You can also rename a sheet by taking value from a cell. Let’s say the value is in cell A1.
Sheets("Sheet1").name = Range("A1").Value
But let’s say you want to name multiple sheets based on the values in a range of cells. In that case, you need to have code like the following.
Sub vba_sheet_rename_multiple()
'These lines define the variables used in the code. wsCount and rCount are used to store counts, ws for a Worksheet object, name for a Range object, and i for a loop counter.
Dim wsCount As Long
Dim rCount As Long
Dim ws As Worksheet
Dim name As Range
Dim i As Long
'This line counts the number of worksheets in the current workbook and assigns it to wsCount.
wsCount = ThisWorkbook.Worksheets.Count
'This line counts the number of rows in the range A1 to A10 and assigns it to rCount.
rCount = Range("A1:A10").Rows.Count
'This checks if the number of worksheets does not match the number of names provided in the range A1 to A10. If they don't match, a message box is displayed, saying "There's some problem with the names provided," and the macro is stopped.
If wsCount <> rCount Then
MsgBox "There's some problem with the names provided."
Exit Sub
Else
'This loop checks each cell in the range A1 to A10. If any cell is left blank, the variable i is incremented by 1.
For Each name In Range("A1:A10")
If IsEmpty(name) = True Then
i = i + 1
End If
Next name
'If any blank cells were found in the range A1 to A10, a message box is shown, saying "There's a blank cell in the names range," and the macro is stopped.
If i > 0 Then
MsgBox "There's is a blank cell in the names range."
Exit Sub
End If
End If
i = 1
'This loop goes through each worksheet in the workbook. It assigns the value of the cell in the range A1 to A10 (corresponding to the loop counter i) to the name of the worksheet. The loop counter i is then incremented by 1.
For Each ws In ThisWorkbook.Worksheets
ws.name = Range("A1:A10").Cells(i, 1).Value
i = 1 + i
Next ws
End Sub
When you run this VBA code, first it will check if the number of cells in the range is equal to the number of sheets that you have in the workbook.
After that, it will check if all the cells in the range that you have specified have values or not. And in the end, it renames all the sheets using those names. Make sure the names in the range are all different, as two sheets can’t have the same name.
It will verify two conditions using IF THEN ELSE and then rename all the sheets.
Rules for Sheet Names
When the new name breaks one of Excel’s rules for sheet names, VBA gives you run-time error 1004. A sheet name:
- Length: can’t be blank and can have up to 31 characters.
- Characters: can’t have any of these characters: \ / ? * : [ ]
- Apostrophe: can’t start or end with an apostrophe (‘).
- Unique: can’t be the same as another sheet in the workbook (the check ignores upper and lower case), and can’t be “History”, which Excel keeps for itself.
Rename a Sheet with a Date
As the slash (/) isn’t allowed in a sheet name, you can’t use a date like 05/11/2026 as it is. Use the Format function to turn the date into text with dashes:
Sub rename_sheet_with_date()
ActiveSheet.Name = "Report " & Format(Date, "dd-mm-yyyy")
End Sub
Add a Prefix to All Sheet Names
To change all the sheet names in one go, loop through the worksheets and build each new name from the old one. The following code adds “2026 – ” in front of every sheet name:
Sub add_prefix_to_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Name = Left("2026 - " & ws.Name, 31)
Next ws
End Sub
The Left function keeps each new name within the 31-character limit.
Frequently Asked Questions
Why do I get error 1004 when renaming a sheet?
The new name breaks one of the rules above: it’s too long, has a character that isn’t allowed, is blank, or another sheet already has it. A protected workbook structure also stops you from renaming sheets.
Do formulas break when I rename a sheet?
No. Excel updates the formulas that refer to the sheet. But VBA code that uses the old name, like Sheets(“Sheet1”), won’t update, so change it or use the sheet’s code name instead.
What is the sheet code name, and does it change?
The code name is the name you see before the brackets in the Project Explorer, like Sheet1 (Data). It doesn’t change when you rename the sheet, so code like Sheet1.Range(“A1”) keeps working. You can change it in the Properties window, under (Name).
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.