Including Worksheet names in formulas

I am trying to figure out how to reference worksheet names that change based on cell values.

For example, we have 52 worksheets - One for each week of the year. We have a date field in the first worksheet and all other sheets calculate the date based on the preceding sheet (cell+7).

We then have a vba script that changes the name of the worksheet to the date of the week.

The problem is if we change the date in the first sheet, the script to change the name of the worksheets does not work until you click in the date field of each sheet.

So the question is how can one have all of the worksheet names updated once the first worksheets date is changed. Hope that makes sense.

Hi you could use a worksheet change event that will kick off from the first sheet where you enter the date and rename all the other sheets:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myWS As Worksheet

If Not Intersect(Target, Range("A1")) Is Nothing Then

    For Each myWS In ThisWorkbook.Worksheets

        If myWS.Name <> Me.Name Then myWS.Name = myWS.Range("A1")
    
    Next myWS

End If

End Sub

The code needs to be placed on the sheets code page so right click on the first sheet's tab and select view code. It will then loop through all the other sheets in the workbook renaming the worksheets based on the value in their A1 cell.

Hope it helps,

Dom

The problem is if we change the date in the first sheet, the script to change the name of the worksheets does not work until you click in the date field of each sheet.

I assumed from this that the code to rename the other sheets was a worksheet change event on each sheet so didn't think a calculation would make much difference. Could be wrong and probably am.

Thank you for the replies. I think I am getting your ideas. However I do not see how this code increased the date by 7 days. Basically the dates need to be 7 days from the previous sheet. The purpose is to set the start date in sheet one and every other sheet adds 7 days to the previous sheet. This gives a sheet name that is for a specific day of the week over the 52 weeks. It would be great that once the first sheet is updated all sheets follow suit.

Hope that makes sense and thank you again for all of the help.

I saw this thread that is perfect, but it does not work with custom formats (i.e. ddmmmyy)

http://www.ozgrid.com/forum/showthread.php?t=92422

What am I missing.

Thanks