Auto rename tab

[orozvik@yahoo.com]

Anyone have a clue how to automatically rename sheet tabs to a name in a cell in another tab?



So, if sheet 1 in A2 has the Value "OAC-125" then the following tab names would be updated with that name as follows:

Sheet 2 - Current Name: DAR Updated to: OAC-125 DAR
Sheet 3 - Current Name: ZKL Updated to: OAC-125 ZKL

you get it ha? I thought maybe some formula can be entered with the concatenate symbol in each tab like this:

Sheet 2 - Sheet 1! A2 & DAR

Any idea guys/gals? thank.s

[macropod]

A formula cannot change a sheet's name. At the very least, a macro would be required.

[:) Sixthsense :)]

Check whether this event code fulfills your expectation

To Add Excel VBA Code to a Workbook / This Workbook Module

1. Copy the code that you want to use
2. Select the workbook in which you want to store the code
3. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
4. In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
5. Right-click on the ThisWorkbook object, and choose View Code
6. Where the cursor is flashing, choose Edit | Paste

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sWs As String, sCell As String

sWs = UCase(Sh.Name)
sCell = UCase(Sh.Range("A2").Value)

If Trim(sCell) <> "" Then
    If Left(sWs, Len(sCell)) <> sCell Then
        On Error Resume Next
            Sh.Name = sCell & " " & sWs
        On Error GoTo 0
    End If
End If

End Sub

[orozvik@yahoo.com]

Hi Sixthsense,
The code works great! Thank you. However, how can I tweak it so that all four tabs are renamed at exactly the moment the text is entered in the first tab in cell D9? So, this would be a change event related to that cell? The tabs would not have the same name as the text entered in cell D9 since the value entered in cell D9 would be appended as the prefix with a space between to the existing tab names like this:

D9 = OAC

Tab 1 = 123 becomes OAC 123
Tab 2 = 789 becomes OAC 789
Tab 3 = 456 becomes OAC 456

Thanks

[orozvik@yahoo.com]

With Sixthsense' help from above and some tweaking of my own I have solved the riddle.

Thus, any time cell D9 has an entry made to it whether adding in an entry or deleting an entry the tabs will update. When adding an entry the tabs will have the entry added as a prefix to the already existing tab name with a space between the newly added prefix and existing tab name.

Also, when the entry in cell D9 is deleted, the tabs will revert back to their original tab names with the prefix being removed.

Pretty cool ha? Here is the code which needs to be added to the sheet object where cell D9 will be updated in project explorer.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sWs As String, sCell As String, shName As String, i As Integer
If Target.Address = "$D$9" And Range("$D$9") <> "" Then
sCell = Range("D9").Value

For i = 2 To 6

sWs = Worksheets(i).Name

On Error Resume Next
shName = sCell & " " & sWs
sWs = shName
Worksheets(i).Name = sWs
On Error GoTo 0
Next i

Else

Worksheets(2).Name = "Add"
Worksheets(3).Name = "Add Remit"
Worksheets(4).Name = "Remove"
Worksheets(5).Name = "Remove Remit"
Worksheets(6).Name = "JE"


End If

End Sub