Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestWs As Worksheet
Dim DestName As String
If Me.Name < "Template" Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub

DestName = Target.Value ' store sheet name
Application.EnableEvents = False
Target.Value = "" ' clear template
Application.EnableEvents = True

Sheets("template").Copy after:=Sheets(Sheets.Count)
Set DestWs = ActiveSheet
On Error Resume Next
DestWs.Name = DestName ' use stored name instead of cell value
On Error GoTo 0
End Sub

Regards

Trevor


"MCSmarties" wrote in message
s.com...
Hello,

I would like Excel to create a new sheet when a value is pasted to an
(empty) cell in a template sheet.
Details:
- the template sheet (which already contains formulae) should treat
anything pasted into a particular range as a VALUE.
- the new sheet should be auto-renamed to the value of a cell in the
template sheet
- the "template" should revert to the state BEFORE data was pasted
into it
- the focus remains on the "new" sheet and any additional data pasted
into it is treated as VALUES.

The discussion below ("Auto Rename Excel Sheets in Workbook")
already comes very close to what I need.
http://groups.google.com/group/micro...006249e7bf4ed4

From the last contribution, I tried the macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestWs As Worksheet
If Me.Name < "Template" Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Intersect(Target, Range("F3")) Is Nothing Then Exit Sub
Set DestWs =
Sheets("template").copy(after:=Sheets(Sheets.Count ))
On Error Resume Next
DestWs.Name = Range("F3").Value
On Error GoTo 0
End Sub

But:
1. I get an error message "Run-time error '424': Object required"
2. The template sheet contains the pasted data at the end
3. There's no "paste as values"handling

Thanks!