Table of Contents
Introduction/Problem Statement/Business Need:
In this article, we will show you How to Rename Sheet in Excel by using the VBA Code. In the below content, you will find the easiest and fastest way to do it instead of manually doing it.
Get Sheet Name:
This code will show the active worksheet name in a message box.
Sub KDataScience_GetWorksheetName() MsgBox ActiveSheet.Name End Sub
This code will show the second worksheet name by Index number.
Sub KDataScience_GetWorksheetNameByIndexNumber() 'Get Worksheet name by giving Index Number MsgBox ThisWorkbook.Sheets(2).Name End Sub
This code will show the last worksheet name in the workbook.
Sub KDataScience_GetLastWorksheetName() MsgBox Sheets(Sheets.Count).Name End Sub
Get Code Name of Worksheet
Each worksheet has a sheet name and a code name. The sheet name is the name that appears in the worksheet tab in Excel. Code name show in VBA Editor.
If we open the VBA Editor window, we will see both names. In the below image we can see that the code name is the name outside the parenthesis and the sheet name is in the parenthesis.
This code will show the code name of a worksheet name
Sub KDataScience_GetCodeNameBySheetName() 'Get code name of sheet input MsgBox Sheets("input").CodeName End Sub
This code will rename the active worksheet
Sub KDataScience_RenameActiveSheet() ActiveSheet.Name = "Change sheet" End Sub
This code will rename the sheet by name
Sub KDataScience_RenameSheetBySheetName() Sheet1.Name = "Sheet Renamed" End Sub
This code will rename the sheet of by given index number of sheet
Sub KDataScience_RenameWorksheetByIndexNumber() 'rename the first sheet to name2 Sheets(1).Name = "Name2" End Sub
If you want to get a name or rename sheet in excel then you may use any of the above code instead of manually doing it. KDataScience would prefer you to use code to delete the sheet without “Deleting” Prompt.