Macro to Rename worksheet based on certeria

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