Rename Tabs Based on a Contents Page

[G16LYB]

Hi

Apologies I know this has been asked before but I cant find anything that seems to cover this specifically.

I have a workbook that I want to have a contents page with a reference number, I've then linked this reference to another sheet in cell A1. I've found some basic code that renames the tab, but if the reference changes in the contents it doesn't update the sheet unless you go into it and click into a cell.

I'll need a lot of tabs in the workbook, possibly 200 with the ability to add more if required.

I'm pretty proficient with excel, but I've zero experience with VBA so detailed instructions would be really helpful.

Thanks

 

[G16LYB]

The details in Contents is coming from another tab called Audit Trail

 

[Fluff]

Ok, are the cells with the sheet names being manually change on the Audit trail tab? If not where are the being changed?

 

[G16LYB]

The audit trail tab will be the source of the data.

Its Purchase Invoices if that helps with context. Each invoice will be given an internal reference, which I want to cascade through the tab names

 

[Fluff]

Ok what cells in the audit trail tab have the sheet names?

 

[G16LYB]

Column F

 

[Fluff]

Ok how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim OldName As String, NewName As String

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("F2:F500")) Is Nothing Then
      If Target.Value = "" Then Exit Sub
      Application.EnableEvents = False
      NewName = Target.Value
      Application.Undo
      OldName = Target.Value
      Target.Value = NewName
      If Not Evaluate("isref('" & OldName & "'!a1)") Then
         MsgBox "Sheet " & OldName & " does not exist"
      ElseIf Evaluate("isref('" & NewName & "'!a1)") Then
         MsgBox "Sheet " & NewName & " already exists"
      Else
         Sheets(OldName).Name = NewName
      End If
   End If
   Application.EnableEvents = True
End Sub

This needs to go in the Audit Trail tab.

 

[G16LYB]

That didnt do anything

 

[Fluff]

Did you put the code in the Audit Trail tab's code module & then change a single cell in f2:f500?

 

[G16LYB]

Yes I did

 

[Fluff]

In that case it should work. Did you get any error messages, or a message box?