rename i worksheet based on cell value

[chriswrcg]

I found this code in the forum and am trying to use it to rename a particular tab in by workbook. The problem is it is trying to rename every tab or worksheet I have and I only need it to change the name of 1 sheet. how can I modify the code to only name the one tab?

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
If Len(ws.Range("B17")) >0 Then
ws.Name = ws.Range("B17").Value
End If
On Error Goto 0
If ws.Name <> ws.Range("B17").Value Then
Msgbox ws.Name & " Was Not renamed, the suggested name was invalid"
End If
Next
End Sub

 

[Fluff]

Which sheet are you trying to rename?
Also is the new name in B17 on that sheet, if not what sheet is it on?

 

[chriswrcg]

actually i just copied the code as i found it. The cell i need to look at is B1 not B17 but otherwise everything else is good. The sheet I am trying to rename is Daily Template ( and always will be) and B1 is on the sheet (its a persons name)

 

[Fluff]

Try

Sub tabname()

    On Error Resume Next
    With Sheets("Daily Template")
        .Name = .Range("B1").Value
        If Err.Number <> 0 Then MsgBox .Range("B1").Value & " Is not a valid sheet name"
    End With

End Sub

 

[Oaktree]

Hi, @Fluff.

Note that your tabname macro would work the first time; but, once "Daily Template" is renamed, it won't work for subsequent runs.

As such, this is a good example for when it's appropriate to reference the sheet's codename. Instead of setting the name of Sheets("Daily Template"), we'd set the name of Sheet4 (or whatever the sheet code name is... you can find it by looking before the parenthesis in the VBA Project window) with

Sheet4.Name = Sheet4.[A1]

or similar. That will still work regardless of how many times said sheet has been renamed.

 

[chriswrcg]

This works great! Thanks. I do have one more question though. I didn't realize that I was going to need two tabs for the same name. One for daily and one for weekly. How could I add the word weekly into the naming process for one tab?

 

[Fluff]

@Oaktree
I hear what you're saying & agree, but as it is a Daily template, I was working on the basis that the workbook would be saved with save as, rather than save.
@chriswrcg
I'm not sure I quite understand. Do you mean that , for instance, Monday to Thursday you save it with one name & on Friday you need a different name? or do you have 2 different sheets that need naming?