Rename sheet based on cell value

[g3org]

Hello guys,
Could someone please help me to modify this code to rename the sheet based on B3 if is nothing in B4? The value will be in B4 basically but if there is Empty, will be in B3.
Many thanks!

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
If Len(ws.Range("B4")) >0 Then
ws.Name = Replace(ws.Range("B4").Value,"/","-")
End If
On Error Goto 0
If ws.Name <> Replace(ws.Range("B4").Value,"/","-") Then
Msgbox ws.Name & " Was Not renamed, the suggested name was invalid"
End If
Next
End Sub

 

[Trebor76]

Hi g3org,

Try this:

Option Explicit
Sub tabname()
    
    Dim ws As Worksheet
    Dim strTabName As String
    
    Application.ScreenUpdating = False
    
    For Each ws In Worksheets
        On Error Resume Next
            strTabName = IIf(Len(ws.Range("B4").Value) = 0, ws.Range("B3").Value, ws.Range("B4").Value)
            ws.Name = Replace(strTabName, "/", "-")
        On Error GoTo 0
        If ws.Name <> Replace(strTabName, "/", "-") Then
            MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
        End If
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[g3org]

Works perfect!! Wish you all the best!

 

[Trebor76]

Thanks for letting us know and you're welcome