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]

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?

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = "A1" Then
    On Error Resume Next
    Me.Name = Target.Value
    On Error GoTo 0
End If
End Sub

Change A1 to the cell of interest then press ALT + Q to close the code window.

 

[chatty56]

Hi Peter<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks for the reply. I tried it, but it doesn’t automatically update the tab name. Perhaps I should explain my particular situation.<o></o>
My spreadsheet has over 60 sheets; each tab is the name of a supplier. On sheet one I have a list of suppliers. A1 on each subsequent sheet is linked to a corresponding cell in this list. <o></o>
Eg: A1 on sheet 2 is linked to A1 on sheet 1, A1 on sheet 2 is linked to A2 on sheet 1 etc.<o></o>
If the user of the spreadsheet edits a name in the list, I need the corresponding tab on the appropriate sheet to automatically refresh to the new name <o></o>
Thanks Chatty<o></o>

 

[VoG]

Try this: right click the Excel logo just to the left of File on the menu bar, select View Code and paste in

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
On Error Resume Next
Sh.Name = Sh.Range("A1").Value
On Error GoTo 0
End Sub

 

[chatty56]

Hi Peter
I tried this (exactly as per your instructions below) on a new worksheet with 3 pages, but nothing happened

 

[19ant]

Hi Jonmo1,

Very useful and works beautifully. Instead of the msg, is it possible to add say an 'a' to the name if it comes up as invalid in the first instance?

Thanks

 

[Trevor3007]

hi,

your code works great but I need to enter additional text after the applicable cell (ie d7) so for example, d7 = monday & plus text " - special". This would then make the tabe name "monday-special".
many thanks in advance.

 

[JudithPeele]

This works great except when the characters exceed 31. How do we prevent errors with text over 31 characters?

 

[Fluff]

One option

Sh.Name = Left(Sh.Range("A1").Value, 31)

 

[Trevor3007]

This works great except when the characters exceed 31. How do we prevent errors with text over 31 characters?

Hi Judith,

I dont know if this is even viable? If it does happen to me, I just shorten accordingly.

Hopefully some more 'knowledgeable' Excel person can sort for you.

KR
Trevor3007

 

[nick_excell]

renaming the tab worked great, i even created a button for it . now i need something else , linked to the same macro.
i need something like an order ticketing system. meaning. first tab should be a draft containing all the column headers, after each cell is populated with order info, i need it to create a new tab with the name of a specific cell(customer name) and delete all the populated info, so another order could be filled.
hope i explained it well. sorry, English is not my native language.