Rename tab based on cell value - Macro

[redjay]

Hi all

I want some VBA code that will cycle through all the worksheets in a workbook and rename each tab/sheet based on the value of a cell in that sheet (B17).

The cell is always B17 but on some of the sheets, B17 is blank, in which case it is fine to not rename.

I have tried the below code but get a 'Name of object'_worksheet' failed' error message.
Any ideas anybody? Thanks

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Name = Range("B17").Value
Next
End Sub

 

[VoG]

Try

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
    With ws
        If .Range("B17").Value <> "" Then .Name = .Range("B17").Value
    End With
Next ws
Next
End Sub

 

[Andrew Poulsom]

Does B17 contain illegal characters /\?*[])?

 

[TheNoocH]

try this

  For Each ws In ActiveWorkbook.Worksheets
          ws.Name = Range("B17").value
  Next ws

 

[Jonmo1]

Well, you're definately going to need some error handling...

Depending on what is in B17 of each sheet.
There are rules regarding how a sheet can be named.

1. There cannot be any duplicates, you can't have 2 sheets with the same name
2. Certain Characters are not allowed in sheet names, like "/" <--this rule is most commonly broken when you try to name your sheet according to a date, dates contain /
3. There is a limit to the length of a sheet name, I think it's 32 characters.

So try

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

 

[redjay]

Thank you both, the code works great....however B17 does indeed contain an illegal character- drat!

The cell has a / character as part of an invoice number. Is there any way I can somehow ask it to rename, but without the / ???

Cheers

 

[Jonmo1]

Try this code, it will replace any / with -

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

 

[redjay]

Excellent!

That works a treat. Thanks Jonmo.

I also need to copy the contents of B19 to another cell A6, across all worksheets. Im guessing this is pretty simple but am again having trouble..?

If anyone can help with that il be v grateful

Thanks.

 

[Jonmo1]

Add in the loop

ws.Range("B19").Copy Destination:=ws.Range("A6")

 

[chatty56]

Hi
I have a similar question, (I also want the tab to be named after a specific cell on each sheet), except I would like this process to happen automatically, so that I don't have to run the macro everytime the cell name changes?