Rename a worksheet

[Patrick Simonds]

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.

 

[Bob Phillips]

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]

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

 

[Greg Wilson]

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

Regards,
Greg