Thank you very much again.
I suppose you really are quite clever when I think about it.
--
Big Rick


"Dave Peterson" wrote:

You could do it that way. But another way is to go through the codenames and
set them that way:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "a1"

Dim iCtr As Long
Dim wks As Worksheet

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each wks In Me.Parent.Worksheets
For iCtr = 1 To 52
If LCase(wks.CodeName) = "sheet" & iCtr Then
wks.Name = Format(Target.Value + (7 * iCtr), "dd mm")
Exit For
End If
Next iCtr
Next wks
End If

ws_exit:
Application.EnableEvents = True
End Sub

==============
But this doesn't sound like something that you'd do very often--once a year when
you're setting things up.

I think I'd remove it from the worksheet_change event and just make a macro that
runs on demand. It would stop user errors (overwriting A1 on Info could cause
trouble).

If you think that's a good idea and you have trouble converting it, just post
back. I'm sure you'll get help.




Big Rick wrote:

Thank you, thank you, thank you, so much.
Absolutely wonderful. Works like a dream. Please can I ask just one more
question though.
If I wanted this on 52 worksheets in one workbook +7, +14, +21 etc
would I require 52 lines of
sheet1.Name = format(target.Value+7,"dd mm")
sheet2.Name = format(target.Value+14,"dd mm") etc
or is there an easier way.
Thank you again for all your time and effort. I really do appreciate it.
--
Big Rick

"Dave Peterson" wrote:

Put the worksheet change into the info worksheet. But you'll have to use the
codename of the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "a1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
sheet99.Name = format(Target.Value + 7, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

If you go into the VBE, select your project, select the sheet that changes name,
then hit F4 (to see the properties, you'll see a "(name)" property. Use that
name.


--

Dave Peterson