Sheet Name Change

[jlawrenz]

Can you set a sheet to change the sheet name to whatever is in a particular cell?

 

[mikerickson]

Without VBA, no. Why do you want to do that?

 

[JoeMo]

You need VBA for this. Assuming the sheet name is in Cell A1 of the sheet, right-click the sheet tab and select View Code. Then paste the code below into the white space in the VBA editor that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then Me.Name = Target.Value
End Sub

Each time a new name is entered in A1, the sheet tab name will change.

 

[jlawrenz]

That worked. I use excel to do my construction daily logs. Everyday I copy the last days daily sheet and I rename the sheet that days date. I want it to change when I put the days date in the log.

That VBA work but I had to put the date with dots (1.1.10) I couldn't use - or /

 

[catcherman]

You need VBA for this. Assuming the sheet name is in Cell A1 of the sheet, right-click the sheet tab and select View Code. Then paste the code below into the white space in the VBA editor that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then Me.Name = Target.Value
End Sub

Each time a new name is entered in A1, the sheet tab name will change.

What if the cell is on a different worksheet?

I have looked through the forums and haven't been able to find the answer.

I have cells B21:B25 in Sheet1 that I want to use to name the worksheet names for Sheet2:Sheet6.

Thanks.

 

[JoeMo]

What if the cell is on a different worksheet?

I have looked through the forums and haven't been able to find the answer.

I have cells B21:B25 in Sheet1 that I want to use to name the worksheet names for Sheet2:Sheet6.

Thanks.

Put this code into a module for Sheet1. Note there's no error trapping in this, so you need to avoid making duplicate entries in cells B21:B25 on sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B21:B25"), Target) Is Nothing Then
    For Each sh In ThisWorkbook.Sheets
        If Len(sh.CodeName) = 6 Then
            If Right(sh.CodeName, 1) Like "[2-6]" Then
                sh.Name = Range("B21").Offset(Val(Right(sh.CodeName, 1)) - 2, 0).Value
            End If
        End If
    Next sh
End If
End Sub