I have a workbook with 104 worksheets (2 worksheets for each week of the
year) which I use as a Template. Each worksheet has a date in cell A4. Each
year I have to create a new years workbook by changing the date on the 1st
worksheet (cell A4) (all other worksheets update based on the date on the
first worksheet). Is there any way to have each worksheet rename to the date
in its respective cell A4? The format would be dd mmm yy.

For the first worksheet add


Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Sheet1
If Target.Address = "$A$4" Then
If IsDate(Target.Value) Then
For Each sh In ThisWorkbook.Worksheets
sh.Name = Format(sh.Range("A4").Value, "ddd-mm-yyyy")
Next sh
End If
End If
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Patrick Simonds" wrote in message
...
I have a workbook with 104 worksheets (2 worksheets for each week of the
year) which I use as a Template. Each worksheet has a date in cell A4.
Each
year I have to create a new years workbook by changing the date on the 1st
worksheet (cell A4) (all other worksheets update based on the date on the
first worksheet). Is there any way to have each worksheet rename to the
date
in its respective cell A4? The format would be dd mmm yy.

Thanks for your reply

I tried your code and get the following error:

Run Time Error 13
Type mismatch

The following line is highlighted:

For Each sh In ThisWorkbook.Worksheets

One thig I should have noted was that Sheet One does not need to be renamed.
I tried deleteing Sheet one to see if it was causing the probem but still
got an error message:

Run Time Error 13
Type mismatch

The following line is highlighted:

Next sh

"Bob Phillips" wrote in message
...
For the first worksheet add


Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Sheet1
If Target.Address = "$A$4" Then
If IsDate(Target.Value) Then
For Each sh In ThisWorkbook.Worksheets
sh.Name = Format(sh.Range("A4").Value, "ddd-mm-yyyy")
Next sh
End If
End If
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Patrick Simonds" wrote in message
...
I have a workbook with 104 worksheets (2 worksheets for each week of the
year) which I use as a Template. Each worksheet has a date in cell A4.
Each
year I have to create a new years workbook by changing the date on the
1st
worksheet (cell A4) (all other worksheets update based on the date on the
first worksheet). Is there any way to have each worksheet rename to the
date
in its respective cell A4? The format would be dd mmm yy.

I think Bob meant "Dim sh As Worksheet" instead of "Dim sh As Sheet1"

Regards,
Greg

"Patrick Simonds" wrote:

Thanks for your reply

I tried your code and get the following error:

Run Time Error 13
Type mismatch

The following line is highlighted:

For Each sh In ThisWorkbook.Worksheets

One thig I should have noted was that Sheet One does not need to be renamed.
I tried deleteing Sheet one to see if it was causing the probem but still
got an error message:

Run Time Error 13
Type mismatch

The following line is highlighted:

Next sh

"Bob Phillips" wrote in message
...
For the first worksheet add


Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Sheet1
If Target.Address = "$A$4" Then
If IsDate(Target.Value) Then
For Each sh In ThisWorkbook.Worksheets
sh.Name = Format(sh.Range("A4").Value, "ddd-mm-yyyy")
Next sh
End If
End If
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Patrick Simonds" wrote in message
...
I have a workbook with 104 worksheets (2 worksheets for each week of the
year) which I use as a Template. Each worksheet has a date in cell A4.
Each
year I have to create a new years workbook by changing the date on the
1st
worksheet (cell A4) (all other worksheets update based on the date on the
first worksheet). Is there any way to have each worksheet rename to the
date
in its respective cell A4? The format would be dd mmm yy.