Rename Worksheet Dynamically in Excel

Microsoft Excel events can be used to rename a worksheet based on a value in a cell. Depending on what kind of manipulations are done with a cell driving a worksheet name, it needs to pick up a specific event to trigger renaming.

Good candidates for events are.

• SelectionChange
• Calculate
• Change

The best choice is Change worksheet event as it is triggered by any changes in a cell.

Let's consider a case when a cell is located in the same worksheet which we are going to rename. The cell is A1. A new workbook contains only 1 worksheet. The sample is developed in Microsoft Office Processional Plus 2016.

1. Open your Excel spreadsheet, then go to VBA Editor pressing Alt-F11 combination.

   

2. Add Change event procedure selecting Worksheet in the first dropdown list and Change in the second one.

   

3. Add code to the event.

   Set CellWithNewWorksheetname = Range("A1")
   If CellWithNewWorksheetname = "" Then Exit Sub
   Worksheets(1).Name = CellWithNewWorksheetname
   

   The event procedure is.

   

4. Return back to Excel and enter value in A1 cell.