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!
This Tutorial Covers:
ToggleRename 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.
Also read: VBA to Hide or Unhide Sheets
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
Also read: Rename Files Using VBA
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: