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
