Oct 04 2019 07:46 PM
I have the following VBA formula to automatically change the tab name based on a cell value:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C5" Then Me.Name = Target.Value
End Sub
I have this formula in C5 "=CONCATENATE(TEXT($K$1,"mmm")," ",TEXT($K$1,"dd")," - ",TEXT($K$2,"mmm")," ",TEXT($K$2,"dd"))" - K1 being a beginning date (eg. 10/4/2019) and K2 being an ending date (eg, 10/10/2019) so resulting value is "Oct 4 - Oct 10" which is what I want the tab named. This part does automatically update. However, the macro to name the tab only works if I a) manually enter the information or b) press F2 and then enter (effectively editing the cell).
Is there a way to change the macro to automatically update the tab, or will I need to press F2 each time?
Oct 04 2019 10:02 PM
Hi,
Try this code instead:
Private Sub Worksheet_Calculate()
Me.Name = Range("C5").Value
End Sub
Regards