How to rename or change name of a worksheet using VBA Excel

Objective

To rename a worksheet using VBA Excel.

Approach

Here we loop through each worksheet in the active workbook. Then we check if the worksheet “Test_1” exists or not. If the worksheet is found, then code renames the worksheet. Here we have renamed the already existing worksheet “Test_1” to “Test_2”.

Once we have renamed the worksheet we have applied “if” condition to display a message if worksheet is renamed or not. If worksheet did not exist, then it will display a message.

Code

Sub RenameWorksheet()

Dim ws As Worksheet
Dim sheetFound As Boolean
sheetFound = False

'Loop thorugh each worksheet in the workbook to check if worksheet Test_1 exists
    
For Each ws In ThisWorkbook.Worksheets
If "Test_1" = ws.Name Then

'Rename the worksheet Test_1 to Test_2
Sheets("Test_1").Name = "Test_2"
sheetFound = True
End If
Next ws

'Display message if worksheet is renamed
If sheetFound = True Then
MsgBox "Worksheet renamed"
Else
MsgBox "Worksheet does not exist"
End If

End Sub

 

Post you may like

List the names of folder and subfolder and save in a file using VBA Excel

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.name