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.
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.
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.