Forum Discussion
Worksheet tabs to include date from a cell
Hello ,
I have a worksheet with four tabs. Cell H1 in both tabs 1 and three contains a date. I would like to include that date in the tab name in the following format: for Tab 1 "AR - as of (the date in H1)" and for Tab 3 "AP - as of (the date in H1)". The dates in the tabs would update whenever the date in cell H1 is updated. I am using Microsoft 365 Apps for Enterprise, version 2510, Build 19328.20178. Thank you for your assistance.
5 Replies
You can do this with a simple VBA macro, since sheet names can’t be linked directly to cells. Add this code to the relevant sheets in the VBA editor (Alt+F11):
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("H1")) Is Nothing Then
Dim dt As String
dt = Format(Me.Range("H1").Value, "dd-mmm-yyyy")
Select Case Me.CodeName
Case "Sheet1" ' adjust to your sheet code name
Me.Name = "AR - as of " & dt
Case "Sheet3"
Me.Name = "AP - as of " & dt
End Select
End If
End Sub
Whenever H1 changes, the tab name updates automatically.https://learn.microsoft.com/en-us/office/vba/excel/concepts/workbooks-and-worksheets/name-a-worksheet-by-using-a-cell-value
Sample available at https://1drv.ms/x/c/1cd824d35610aacb/EeJgSJdWTeVFhWfPn_xLwa4BNFhpq2DXtj92XyZgkjnR_Q?e=IhPJOt (file to download - won't work with Excel Wed/Online)
NB: Not a VBA expert at all
You will need to use VBA coding to achieve this.
I thought would be the case. Do you know what the VBA coding would be? Thank you!
