Renaming a Worksheet Based on Partial Values from Multiple Cells

[reberryjr]

I've searched on the internet and haven't been able to find a solution that fits my needs. I would like to rename a worksheet based on part of 2 cell values.
Cell B2 value = 4/1/2024 and is formatted as MMM-YY
Cell B26 value = 2/1/2024 and is formatted as MMM-YY

When I update cell B2 to 5/1/2024, cell B26 will automatically update to 3/1/2024. I would like for the worksheet to be renamed 03.24 - 05.24. I can get by with 3.24 - 5.24 if needed.

My initial thought was to somehow incorporate Left(B2,2); but that won't work because the value is a single digit. Now I'm thinking some sort of split at the /, but I'm not positive.

 

[Cubist]

Insert the code into the Sheet module. Try on a copy.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
        Dim ws As Worksheet
        Dim date1 As String
        Dim date2 As String
        Dim newName As String
        Set ws = Me
       
        date1 = Format(Me.Range("B2").Value, "MM.YY")
        date2 = Format(Me.Range("B26").Value, "MM.YY")
        newName = date2 & " - " & date1

        On Error Resume Next
        ws.Name = newName
        On Error GoTo 0
    End If
End Sub