Dim index As Integer
For index = 1 To 18
Sheets(index).Name = Sheets(index).Range("A2").Value
In some cases, you may need to rename a worksheet based on a specified cell value. In this article, we will show you methods of making sheet tab name equal to cell value in Excel.
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
With the following VBA code, you can make sheet tab name equal to cell value automatically.
1. Right click the sheet tab which you want to make the sheet name equal to cell value, then click View Code from the right-clicking menu. See screenshot:
2. Copy and paste below code into the Code window, and then press Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.
VBA code: Make sheet tab equal to cell value
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then ActiveSheet.Name = ActiveSheet.Range("A1") End If End Sub
Note: In the code, A1 is the cell which contains the value you need to use as sheet name. You can change it as you need.
From now on, when the value in cell A1 changed, the sheet tab name will be changed equally as well.
In this section, we introduce you the Rename Multiple Worksheets utility of Kutools for Excel. With this utility, you can easily rename worksheets with certain cell’s value in Excel.
1. Click Kutools Plus > Worksheet > Rename Multiple Worksheets. See screenshot:
2. In the Rename Multiple Worksheets dialog box, you need to do as follows.
1). Check the worksheet names you want to rename in the Worksheets box (you can select one sheet or multiple sheets).
2). Select Replace original sheet name box in the Rename Options section.
3). If you want to rename a worksheet or multiple worksheets with specified cell’s value, please select the From specific range option, and select the range of cells that you want to rename the sheets based on.
3). Click the OK button. See screenshot:
You can see the selected worksheets are renamed by the specified range value immediately as below screenshot shown.
1. You can use the Filter function to easily filter out the needed worksheet if there are lots of worksheets exist.
2. If you want to rename multiple worksheets with a certain cell value in each sheet. For example, sheet1 will be equal to its cell A1’s value, and sheet2 will also be equal to its cell A1’s value. Please select worksheets in the Worksheets box, then select the Rename worksheets with specific cell option, and specify cell A1 in the box.
3. The sheet name won’t be changed automatically with the cell value changes.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!