Naming Worksheets

[wdlyons]

Hi

I have a spreadsheet with 8 worksheets and would like to automatically name the last 7 worksheets Sheet3,4,5,6,7,8 and 9) from cells located on the first worksheet(Chart) which have been highlighted (see image).

I have been able to find reference to naming worksheets from cells on the same sheet but not from cells on another sheet.
spreadsheet.jpg

I am using the following macro (B18 is the first cell range):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strNew As String, strOld As String
If Target.Cells.Count > 1 Then Exit Sub

With Sheets(1).Range("B18")
On Error Resume Next
If Not (Application.Intersect(Target, .DirectPrecedents) Is Nothing) Then
strOld = Me.Name
strNew = CStr(.Value)
Me.Name = strNew



End If
On Error GoTo 0
End With

End Sub

but cannot get it to work.

Any assistance or advice would be appreciated

cheers

Warwick

[alansidman]

Here's a link from earlier today

http://www.excelforum.com/excel-prog...-workbook.html