When you add a new sheet in a workbook, you have the option to name it. But you can also rename it 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 that to tell VBA the value you want to use to rename the sheet.
- In the end, type the name of the sheet that you want to use. But make sure to use specify a name using the double quotation marks “mySheet”.
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
Rename the Activesheet using a VBA Code
If you want to rename the active sheet, in that case, 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 that you have on the fifth number, the code would be.
Sheets(5).Name = "mySheet5"
When you run the above macro, it renames the sheet that is on the fifth number.
Check if the Sheet Exists before Renaming
If you try to rename a worksheet that doesn’t exist, VBA will show you an error, 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 object.
'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 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 cells in the range are 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, rename all the sheets using those names.
It will verify two conditions using IF THEN ELSE and then rename all the sheets.