Sheet Names

[Jborg]

Might be possible with VBA??

I have a workbook with 12 sheets for every Month. Sheet Names are January-2019 etc all the way to December-2019

In every sheet in cell R3 i have written 2019.

Is there a way to change 2019 to 2020 in cell R3 and the sheet name will change to January-2020??

Thanks in advance for your help

 

[steve the fish]

Put this in the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim rng As Range

If InStr(Sh.Name, "-") > 0 Then
    Set rng = Intersect(Target, Sh.Range("R3"))
    If Not rng Is Nothing Then
        If Len(rng.Value) > 0 Then
            Sh.Name = Split(Sh.Name, "-")(0) & "-" & rng.Value
        End If
    End If
End If

End Sub

 

[Jborg]

Steve the Fish

Thanks Alot. It works like a charm

I appreciate your fast response.