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 the sheet that you want to rename using the worksheet object.
- After that, you need to use (.Name) to access the name property that you want to change.
- 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.
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
Rename the Activesheet
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()
Dim ws As Worksheet
Dim mySheet As String
Dim SheetName As String
mySheet = InputBox("enter the name of the sheet that you want to rename.")
SheetName = InputBox("Enter new name for the sheet.")
For Each ws In ThisWorkbook.Worksheets
If mySheet = ws.Name Then
ws.Name = SheetName
End If
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()
Dim wsCount As Long
Dim rCount As Long
Dim ws As Worksheet
Dim name As Range
Dim i As Long
wsCount = ThisWorkbook.Worksheets.Count
rCount = Range("A1:A10").Rows.Count
'Checks if the count of the names provided is less _
or more than the sheets in the workbook
If wsCount <> rCount Then
MsgBox "There's some problem with the names provided."
Exit Sub
Else
'Check if any of the cells in the name range is empty.
For Each name In Range("A1:A10")
If IsEmpty(name) = True Then
i = i + 1
End If
Next name
If i > 0 Then
MsgBox "There's is a blank cell in the names range."
Exit Sub
End If
End If
'rename each sheet using the value from the range cell by cell.
i = 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.