Macro to rename worksheet tabs based on cell contents but only certain worksheets

[ShanaVT]

I have found many macros on the internet that would allow me to rename all the tabs in a workbook based on cell contents but I need to do this only for a subset of the worksheets. In my workbook I have let's say 5 tabs named (A,B,C,D,E) then I have a series of tabs named X1, X2, X3, X4...X80. It is just the X1-X80 tabs that I want to build the macro for but the title of the tab would be the same cell in those sheets (A6). Also A6 is a formula not text if that matters. Lastly to make it more difficult some names in cell A6 will be too long for a tab name - not sure if some kind of truncation can be written into the macro. Any help would be much appreciated!

Thanks!

 

[Momentman]

If your sheets are actually named X1 to X80, then this should work, the LEFT function in the code trims the name to 20 characters, you can adjust as necessary

Sub RenameSht()
    Dim I As Integer
    For i = 1 To 80
        Worksheets("X" & i).Name = Left(Worksheets("X" & i).Range("A6"), 20)
    Next i
End Sub

 

[ShanaVT]

Thank you! This is great. I was wondering if it would be possible to write two more things into the macro.

1. Some of the names have "/" in them - is there a way to write the macro where it can change those to "_" in the worksheet tab so it doesn't cause an error when running the macro?
2. I want the macro to stop running when it hits the first A7 cell that reads "XXX" instead of a title and keep those tabs listed with their respective numbers (ie. "X52") instead of changing them all to "XXX" - is that possible?

Thanks again!

 

[Momentman]

Give this a try

Sub RenameSht()
    Dim wf As WorksheetFunction, i As Integer
    Set wf = WorksheetFunction.Substitute
    For i = 1 To 80
        If Worksheets("X" & i).Range("A7") <> "XXX" Then
            Worksheets("X" & i).Name = Left(wf(Worksheets("X" & i).Range("A6"), "/", "_"), 20)
        Else
            Exit For
        End If
    Next i
End Sub

 

[ShanaVT]

Thanks. I am getting an error on this one "Compile error: Argument not optional." and then it highlights the word Substitute.

 

[Momentman]

Apologies, my mistake. Try this way

Sub RenameSht()
    Dim wf As WorksheetFunction, i As Integer
    Set wf = Application.WorksheetFunction
    For i = 1 To 80
        If Worksheets("X" & i).Range("A7") <> "XXX" Then
            Worksheets("X" & i).Name = Left(wf.Substitute(Worksheets("X" & i).Range("A6"), "/", "_"), 20)
        Else
            Exit For
        End If
    Next i
End Sub