Auto update Worksheet Tab Name when changing the Cell value from main Worksheet

[skyflare]

Hi,

can anyone help me to my problem?

Attached is the sample template that I'm using everyday..

here's what i want

If I change the "C8" (from the Summary Tab) it will reflect to "A1" (from Worksheet2) and then the Worksheet2 tab will automatically renamed based on the data on "A1" (from Worksheet2)

If I change the "C9" (from the Summary Tab) it will reflect to "A1" (from Worksheet3) and then the Worksheet3 tab will automatically renamed based on the data on "A1" (from Worksheet3)

If I change the "C10" (from the Summary Tab) it will reflect to "A1" (from Worksheet4) and then the Worksheet4 tab will automatically renamed based on the data on "A1" (from Worksheet4)

If I change the "C11" (from the Summary Tab) it will reflect to "A1" (from Worksheet5) and then the Worksheet5 tab will automatically renamed based on the data on "A1" (from Worksheet5)

If I change the "C12" (from the Summary Tab) it will reflect to "A1" (from Worksheet6) and then the Worksheet6 tab will automatically renamed based on the data on "A1" (from Worksheet6)

e.g the value of "C8" is "100001" then if you will go to "A1" from Workseet2 i added some formula like ="GSA Freight Invoice"&" - "&H6 (H6 is the Invoice No field from my template and its linked from "C8" (Summary Tab)) it looks good but the problem is the Worksheet2 tab name is not changing. It will work only if i will go per sheet and press/double click cell "A1".. is there any way that i will not do it manually one bye one??


here's the code per each worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngShtName As Range
Set rngShtName = Range("A1")
If Not (Intersect(Target, rngShtName) Is Nothing) Then
On Error Resume Next
Me.Name = rngShtName.Value
On Error GoTo 0
End If
End Sub

Thanks

Sky

[AlphaFrog]

The Worksheet_Change event procedures in the invoice sheets are not triggered by a formula (cell A1) calculation. They are triggered when the user makes a change to the sheet e.g. you double click on a cell.

If you are manually entering\changing the Invoice numbers on the Summary sheet, you can have a Worksheet_Change event procedure for the Summary sheet and it can change the other sheet names.

Please Login or Register  to view this content.

[skyflare]

Hi AlphaFrog,

You're the man! thanks a lot! works like a charm..

Thanks

Sky