Updating tab name by cell reference

[LauchB]

Hello,

I have entered the following code into the tab view code field and it works well when I manually change this field. However D1 references another sheet and when I change it in the other sheet D1 changes, but not the sheet tab name, ideas?



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "D1" Then Me.Name = "Report Summary " & Target.Value
End Sub

 

[MARK858]

Put it a Worksheet_Calulate event rather than a Worksheet_Change event maybe.

 

[tyija1995]

Hey,

What does D1 reference exactly in your report summary tab? You could set up 2 pieces of code with a worksheet change and a worksheet activate.

Example:

Lets say D1 in your summary tab references Sheet2!D1 then:

Sheet2 code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "D1" Then Sheets(1).Activate
    Sheets("Sheet2").Activate
End Sub

Sheet1 (report summary) code:

Private Sub Worksheet_Activate()
    Me.Name = "Report Summary " & Range("D1").Value
End Sub

Here I assume the report summary is the leftmost tab in the workbook

 

[LauchB]

Hey,

What does D1 reference exactly in your report summary tab? You could set up 2 pieces of code with a worksheet change and a worksheet activate.

Example:

Lets say D1 in your summary tab references Sheet2!D1 then:

Sheet2 code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "D1" Then Sheets(1).Activate
    Sheets("Sheet2").Activate
End Sub

Sheet1 (report summary) code:

Private Sub Worksheet_Activate()
    Me.Name = "Report Summary " & Range("D1").Value
End Sub

Here I assume the report summary is the leftmost tab in the workbook

Hi,

Thanks for this. D1 references a timeframe- eg 2020, then other "report summaries" work from that, 2020 -1 etc, I want each tab name updated. It is dynamic so you can also just pick a week and have this week, previous week etc.