Macro to Rename worksheet based on certeria

[itzme218]

I was able to find this code below, but instead of renaming to the string in cell B5, I want to search Column A for the row that contains word "total", then rename the Worksheet to the text string coming after words "Total for". Any ideas how I can accomplish this? TIA

Sub RenameSheet()
Dim rs As Worksheet
For Each rs In Sheets
rs.Name = rs.Range("B5")
Next rs
End Sub

[#2]

@itzme218 there are some drawbacks you should think about when coding something like this. Off the top of my head, let me list the potential errors:

• You may not find the value you're looking for where you're looking for it
• The sheet name may already exist
• You may have illegal characters for the desired sheet name
• Duplicate sheet names may arise

While these are able to be mitigated, the onus is on you to answer them.

The below code will do what you want. It's not bulletproof.

Public Sub RenameWorksheets()

    Dim Sheet As Worksheet
    Dim FoundCell As Range
    Dim SheetName As String
    
    For Each Sheet In ThisWorkbook.Worksheets
        
        On Error Resume Next
        Set FoundCell = Sheet.Range("A:A").Find(What:="Total for*", LookAt:=xlPart)
        On Error GoTo 0
        
        If Not FoundCell Is Nothing Then
            SheetName = Left(Replace(FoundCell.Value, "Total for ", ""), 31)
            If Not WorksheetExists(SheetName, ThisWorkbook) Then
                Sheet.Name = SheetName
            End If
            Set FoundCell = Nothing
        End If
        
    Next Sheet

End Sub


Function WorksheetExists( _
         ByVal SheetName As String, _
         Optional TargetBook As Workbook _
         ) As Boolean

    If TargetBook Is Nothing Then
        If ActiveWorkbook Is Nothing Then Exit Function
        Set TargetBook = ActiveWorkbook
    End If
    On Error Resume Next
    WorksheetExists = CBool(Len(TargetBook.Worksheets(SheetName).Name) <> 0)
    On Error GoTo 0

End Function

HTH

[#3]

@Zack Barressethank you for the pointers. I was able to get the code to work! You even thought about to included maximum 31 characters for worksheet name. Many thanks for your help @Zack Barresse