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

ShanaVT

Board Regular
Joined
May 12, 2010
Messages
85
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!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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

Board Regular
Joined
May 12, 2010
Messages
85
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

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
Give this a try
Code:
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

Board Regular
Joined
May 12, 2010
Messages
85
Thanks. I am getting an error on this one "Compile error: Argument not optional." and then it highlights the word Substitute.
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
Apologies, my mistake. Try this way
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,611
Messages
5,523,889
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top