Renaming sheets in Excel is a common task, and with VBA, you can do this at scale with some very simple code.
While it’s easy to rename one or two worksheets, when you automate it with VBA, it can save you a lot of time.
In this article, I will cover some examples where you can use VBA code to rename sheets in Excel.
So, grab your coffee, sit back, and let’s get started! It’s time to become an Excel pro!
Rename Active Sheet
Below is the VBA code that will rename the active sheet to “Sales Data”
Sub RenameActiveSheet() ' Change Active sheet name ActiveSheet.Name = "Sales Data" End Sub
While this works, it’s not very useful as it will take you less effort to rename the worksheet manually.
However, when we extend this concept, we can do some amazing stuff.
For example, below is the VBA code which would rename the active sheet by using the value in cell A1:
Sub RenameActiveSheet() ' Change Active sheet name based on cell value ActiveSheet.Name = Range("A1").Value End Sub
Note: This code will give you an error in case the cell A1 is empty.
While you may still think this isn’t a big-time save, when we extend this concept further, it becomes useful.
Imagine running a code, and all the sheets in the workbook are renamed using the cell value in the sheet. Now, that’s something you can not you can do faster manually.
So, now that the concept of renaming a sheet is clear, let’s look at more practical examples.
Also read: How to Rename a Sheet in Excel (Shortcuts)
Rename the First Sheet (using the Index Number)
Below is the VBA code that would change sheet name of the first sheet to Sales Data”
Sub RenameFirstSheet() ' Rename the worksheet ThisWorkbook.Sheets(1).Name = "Sales Data" End Sub
In the above VBA code, we have used the sheet index number ThisWorkbook.Sheets(1) to refer to the first sheet.
You can change the number in the parenthesis, and it will refer to the worksheet in that position in the workbook.
Rename All Sheets with Cell Value
Sub RenameSheetsCellValue() ' Declare variables Dim ws As Worksheet Dim cellValue As String ' Loop through each worksheet in the workbook For Each ws In ThisWorkbook.Worksheets ' Read the value from cell A1 of the current worksheet cellValue = ws.Range("A1").Value ' Check if the cell is not empty If cellValue <> "" Then ' Rename the worksheet with the cell value ws.Name = cellValue End If ' Move to the next worksheet in the loop Next ws ' End the subroutine End Sub
The above code loops through all the sheets in the workbook and renames the sheet using the value in cell A1 of each sheet.
It also uses an IF statement to check whether the cell is empty. If the cell is empty, it leaves that worksheet without renaming it.
Also read: VBA Clear Sheet
Rename Sheets To File Name
The below VBA code will change the name of the active sheet to the file name:
Sub RenameSheetToFileName() ' Declare a variable to hold the file name Dim fileName As String ' Extract the file name from the workbook's full path fileName = Mid(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\") + 1) ' Remove the file extension from the file name fileName = Left(fileName, InStrRev(fileName, ".") - 1) ' Rename the active sheet to the file name ActiveSheet.Name = fileName End Sub
The above code first extracts the file name from the full file path and then assigns it to a variable called fileName. It then renames the active sheet to the value that the fileName variable holds.
Note: For this code to work, you must have saved your file somewhere. If it’s a new workbook that has never been saved, it will give you an error (Run-time error 5)
Rename Sheets Based On a List
If you want to rename your sheets based on a list of names you already have in a range, you can easily do that with VBA.
Below, I have some names that I want to use to rename the sheets. So, I want the first sheet to be renamed to Sales, the second to Marketing, and so on.
Here is the VBA code to do this:
Sub RenameSheetsBasedOnList() ' Declare a variables Dim ws As Worksheet Dim i As Integer ' Initialize the counter variable to 1 i = 1 ' Loop through each worksheet in the workbook For Each ws In Worksheets ' If cell is not empty, rename the worksheet using cell value If Range("A1").Offset(i - 1, 0) <> "" Then ws.Name = ActiveSheet.Range("A1").Offset(i - 1, 0) End If ' Increment the counter variable i = i + 1 Next ws End Sub
The above code uses two variables – counter (i) and a worksheet variable (was).
It loops through each worksheet in the workbook. Inside the loop, it checks whether the cell at an offset from A1 (using the counter) is empty.
If it’s not empty, the code renames the worksheet (ws.Name) based on the value in that cell.
The counter (i) increments by one after each iteration of the loop, allowing you to go through all worksheets and rename them based on the list starting from cell A1 in the active sheet.
Also read: Protect and Unprotect Sheet Using VBA
Rename Sheet in an Open Workbook
Below is the VBA code you can use to rename a sheet in an already open workbook.
Sub RenameSheetInOpenWorkbook() ' Declare variable Dim targetwb As Workbook ' Set the workbook variable to the already open workbook Set targetwb = Workbooks("OpenWorkbook.xlsx") ' Rename the worksheet targetwb.Sheets("Sheet1").Name = "Sales Data" End Sub
In the above code, I have used a variable targetwb and used it to store the open workbook object in which I want to rename the sheet.
Then, I refer to the sheet that I want to rename (which is Sheet1 in this case) and change its name.
Rename Sheet in a Closed Workbook
Below is the VBA code that allows you to rename a worksheet in a closed workbook.
Since VBA cannot work with closed workbooks, the code has to first open the workbook, rename the sheet, and then close it back again.
Sub RenameSheetInClosedWorkbook() ' Declare Workbook and Worksheet variables Dim targetwb As Workbook Dim targetws As Worksheet ' Open the workbook that is currently closed Set targetwb = Workbooks.Open("C:\Users\sumit\Downloads\Example.xlsx") ' Reference the worksheet to be renamed Set targetws = targetwb.Sheets("Sheet1") ' Rename the worksheet targetws.Name = "New Sheet Name" ' Save and close the workbook targetwb.Close SaveChanges:=True End Sub
Rename Sheet If It Exists
Below is the VBA code that first checks whether the sheet exists in the workbook or not, and if it exists, then it will rename it.
Sub RenameSheetIfExists() ' Declare variables Dim wb As Workbook Dim ws As Worksheet Dim sheetExists As Boolean ' Set reference to the workbook Set wb = ThisWorkbook ' Initialize the flag as False sheetExists = False ' Loop through each worksheet to check if it exists For Each ws In wb.Sheets If ws.Name = "OldSheet" Then sheetExists = True Exit For End If Next ws ' Rename the sheet if it exists If sheetExists Then wb.Sheets("OldSheet").Name = "NewSheet" MsgBox "Sheet has been renamed" Else MsgBox "Sheet does not exist." End If End Sub
Using the above code, I want to rename a sheet with the name “OldSheet” to “NewSheet”.
To do this, I’ve used a loop to go through each sheet and check the name of the sheet. If the name of the sheet is “OldSheet”, it gets renamed to “NewSheet”, and a message is shown saying, “Sheet has been renamed“
Rename Active Sheet With Today’s Date/Time
Below is the VBA code that will rename the active sheet to the current date
Sub RenameSheetWithTodaysDate() ' Declare a variable to hold the formatted date string Dim CurrDate As String ' Get today's date and format it as YYYY-MM-DD CurrDate = Format(Date, "dd-mmm-yyyy") ' Rename the active sheet to the formatted date string ActiveSheet.Name = CurrDate End Sub
In the above code, I have used the Date function to get the current date and then used the Format function to format the date in the dd-mmm-yyyy format.
This is to ensure the sheet name will be valid, as Excel sheet names cannot contain certain characters like slashes (/).
Finally, the ActiveSheet.Name property is set to this formatted date string, effectively renaming the active sheet to the current date.
Note that Excel requires unique names for each sheet, so if a sheet with today’s date already exists, running this macro will result in an error.
If you want to rename the active sheet to the current date as well as time, you can use the code below.
Sub RenameSheetWithTodaysDateTime() ' Declare a variable to hold the formatted date string Dim CurrDateTime As String ' Get today's date and format it as YYYY-MM-DD CurrDateTime = Format(Now, "dd-mmm-yyyy_HH-MM-SS") ' Rename the active sheet to the formatted date string ActiveSheet.Name = CurrDateTime End Sub
Note: You can not use a colon (:) in the sheet name, so here I used the format HH-SS-MM to show the time.
Add Prefix to all Sheets
Want to add a specific prefix to all the sheets in the workbook? The below code can do it for you.
Sub AddPrefixToAllSheets() ' Declare variables for the worksheet and the prefix string Dim ws As Worksheet Dim prefix As String ' Set the prefix string prefix = "Sales_" ' Loop through all worksheets in the workbook For Each ws In ThisWorkbook.Worksheets ' Add prefix to the worksheet name ws.Name = prefix & ws.Name Next ws End Sub
The above code uses a simple For Next loop to go through each sheet and add the prefix text (which I have assigned to a variable called prefix).
You can use the same logic to append any suffix after the sheet name for all the sheets in the workbook.
Rules for Naming Sheets in Excel
Below are some rules you need to keep in mind when renaming sheets in Excel:
- You can’t use a name that exceeds 31 characters.
- You can not use any of the following characters in the sheet name : / \ ? * [ or ]
- You can not leave the worksheet name blank
- You can not use the same name for more than one worksheet.
- A sheet name cannot start with a space character.
- Sheet names are not case-sensitive. So, Sheet1 and sheet1 are considered the same by Excel.
Other Excel VBA articles you may also like: