Naming a worksheet based on cell data

[compta_8]

Hello I'm wondering if this can be done and if so, I'm looking for a little help.

I have a workbook with 5 worksheets included. Each worksheet has a 7 day calendar Starting with sunday; Sunday's value is in B4.

I want to place a date value in cell B4 of sheet1 and have that value be the name of sheet1 and add one week for every following worksheet . For example, in sheet1 I input 1/1/2005; I would like Sheet1 to be named "Week of 1/1/2005", Sheet2 to be named "Week of 1/8/2005" Sheet3 to be named "Week of 1/15/2005" and so on. Any help or suggestions would be greatly appreciated.

Thanks,
Chris

e-mail compta_8@yahoo.com

[Hellboy]

Hi

You cant use the caracter "/" in the name of your sheet.
You have tu use a diffrent caracter

[compta_8]

"-" can be used instead of "/"

Still need to solve the main problem though.

Thanks,
-Chris

[Leith Ross]

Hi,

Add the following code to each worksheets Worksheet Change Event procedure.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.AddressLocal(False, False) = "B2" Then
ActiveSheet.Name = "Week of " & Format(Target.Value, "dd-mmm-yy")
End If

End Sub

Hope this helps,
Leith Ross

[compta_8]

Much thanks. It works great!

-Chris