Deleting is tricky, there is no delete sheet event.

To put the sheet name in A1, use

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim oWS As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set oWS = Worksheets(.Value)
If oWS Is Nothing Then
Worksheets.Add.Name = .Value
Activesheet.Range("A1").Value = .Value
End If
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RalphB" wrote in message
...
Thank you very much. That worked just fine. I do not understand how it
works
but am happy it does.

I would like to know how to adjust the code so it only works when a name
is
entered into the list. As is, it adds a sheet regarless of what was done
in
the list. I do not want a page added if I delete a name from the list. In
fact I would like for it to remove the supplier sheet when the name is
deleted from the list.

And can the code be changed to enter the name used on the tab into A1 on
the
new page?

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim oWS As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
Set oWS = Worksheets(.Value)
If oWS Is Nothing Then Worksheets.Add.Name = .Value
On Error GoTo 0
End With
End If

ws_exit:
Application.EnableEvents = True
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 email address if mailing direct)

"RalphB" wrote in message
...
I need help writing code that will allow me to enter a name into a
list on
Sheet1 that will triger a macro on Sheet2 that will use the name
entered
on
Sheet1.

The name is for a supplier and the macro would generate a new sheet
providing the supplier's information and carying the suppliers name on
the
page tab.

I am using XP Pro and Excel 2003.