Apr 21 2020 12:23 PM
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
Apr 21 2020 07:00 PM
@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:
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
Apr 22 2020 11:11 AM
@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